PHP - Google API Copy and Paste row keeping the original format

Question

I have 2 google spreadsheet documents, the MainSpreadsheet and SecondarySpreadSheet. And the MainSpreadsheet has a lot of rows and some of them I need to copy and keep the style format. So I need to take some specific rows from the MainSpreadSheet and paste them in the SecondarySpreadSheet using Google API PHP. I was trying by using the class name Google_Service_Sheets_CopyPasteRequest() but I could not manage to solve the problem. Can you guys help me just to copy and paste with the same format style of the MainSpreadsheet? Appreciate your guys time.

$newSpreadSheetID = '12345' ;

$mainSpreadSheetID = '54321';


$client = new \Google_Client();
$client->setApplicationName('NAME');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig('credentials.json');
$service = new Google_Service_Sheets($client);


/**
 * Copy from the Main Sheet
 */
$mainFirstSheetID = null;
$worksheetSheets = $service->spreadsheets->get($mainSpreadSheetID)->sheets;
foreach($worksheetSheets as $sheet){
    $mainFirstSheetID = $sheet->properties['sheetId'];     
}


$copyRange = new Google_Service_Sheets_GridRange();
$copyRange->setSheetId($mainFirstSheetID);
$copyRange->setStartRowIndex(10);
$copyRange->setEndRowIndex(11);
$copyRange->setStartColumnIndex(0);
$copyRange->setEndColumnIndex(400);

/**
 * Paste in the new SHEET
 */
$newSheetID = null;
$worksheetSheets = $service->spreadsheets->get($newSpreadSheetID)->sheets;
foreach($worksheetSheets as $sheet){ 
    $newSheetID = $sheet->properties['sheetId'];     
}

$pasteRange = new Google_Service_Sheets_GridRange();
$pasteRange->setSheetId($newSheetID);
$pasteRange->setStartRowIndex(10);
$pasteRange->setEndRowIndex(11);
$pasteRange->setStartColumnIndex(0);
$pasteRange->setEndColumnIndex(400);


$copypasteRequest = new Google_Service_Sheets_CopyPasteRequest();

$copypasteRequest->setSource($copyRange);
$copypasteRequest->setDestination($pasteRange);


$request = new Google_Service_Sheets_Request(); 
$request->setCopyPaste($copypasteRequest);

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$batchUpdateRequest->setRequests($request);

$newSpreadsheet = $service->spreadsheets->get($newSpreadSheetID);

$mainSpreadsheet = $service->spreadsheets->get($mainSpreadSheetID);

$finalRowRange = $newSpreadsheet->range;
$finalRowStartPosition = strpos($newSpreadsheet->range,':') + 2;
$finalRow = intval(substr($finalRowRange,$finalRowStartPosition));

if($finalRow <= $pasteRange['startRowIndex']){
    $appendDimensionRequest = new Google_Service_Sheets_AppendDimensionRequest();
    $appendDimensionRequest->setSheetId($newSheetID);
    $appendDimensionRequest->setDimension("ROWS");
    $appendDimensionRequest->setLength(1);
    $appendRequest = new Google_Service_Sheets_Request(); 
    $appendRequest->setAppendDimension($appendDimensionRequest);
    $appendEmptyRowBatchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $appendEmptyRowBatchUpdateRequest->setRequests($appendRequest);
    $service->spreadsheets->batchUpdate($newSpreadSheetID, $appendEmptyRowBatchUpdateRequest);
}
$service->spreadsheets->batchUpdate($newSpreadSheetID, $batchUpdateRequest);


$service->spreadsheets_values->update(
    $newSpreadSheetID,
    $finalRow,
    $copypasteRequest
);

user_7108653 · Answer

I believe your goal and your current situation as follows.

You want to copy a row of Spreadsheet of "MAIN SHEET" to the 1st empty row of Spreadsheet of "NEW SHEET".
You want to copy not only values but also the cell formats.
You want to achieve this using googleapis for PHP.
You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

Unfortunately, "CopyPasteRequest" of the batchUpdate method cannot copy from Google Spreadsheet to other Google Spreadsheet. It seems that this is the current specification.
In order to copy not only the values, but also the cell format from Google Spreadsheet "MAIN SHEET" to google Spreadsheet "NEW SHEET", I would like to propose the following flow.
1. Copy the source sheet in the source Spreadsheet to the destination Spreadsheet.
2. Retrieve 1st empty row number of the destination sheet.
3. Copy the values with the format from the copied sheet to the destination sheet. And, delete the copied sheet.

This flow is from my recent answer. But this is the python script. So, in order to use this flow for PHP, I answer a sample script. When above points are reflected to a script, it becomes as follows.

Sample script:

In this sample script, your $service is used. So please add the authorization script. And, please set the variables for running script.

$service = new Google_Service_Sheets($client); // <--- This is from your script.

$copiedRowNumber = 16; // Please set the row number of "MAIN SHEET" that you want to copy.
$sourceSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
$sourceSheetId = "0"; // Please set the source sheet ID.
$destinationSpreadsheetId = "###"; // Please set the destination Spreadsheet ID.
$destinationSheetName = "Página1"; // Please set the destination sheet name.
$destinationSheetId = "0"; // Please set the destination sheet ID.

// 1. Copy the source sheet in the source Spreadsheet to the destination Spreadsheet.
$requestBody = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest(
    array("destinationSpreadsheetId" => $destinationSpreadsheetId)
);
$res1 = $service->spreadsheets_sheets->copyTo($sourceSpreadsheetId, $sourceSheetId, $requestBody);
$copiedSheetId = $res1["sheetId"];

// 2. Retrieve 1st empty row number of the destination sheet.
$res2 = $service->spreadsheets_values->get($destinationSpreadsheetId, $destinationSheetName);
$row = count($res2["values"]);

// 3. Copy the values with the format from the copied sheet to the destination sheet. And, delete the copied sheet.
$requests = [
    new \Google_Service_Sheets_Request([
        'copyPaste' => [
            'source' => [
                'sheetId' => $copiedSheetId,
                'startRowIndex' => $copiedRowNumber - 1,
                'endRowIndex' => $copiedRowNumber,
            ],
            'destination' => [
                'sheetId' => $destinationSheetId,
                'startRowIndex' => $row,
                'endRowIndex' => $row + 1
            ],
            'pasteType' => 'PASTE_NORMAL',
        ],
    ]),
    new \Google_Service_Sheets_Request([
        'deleteSheet' => ['sheetId' => $copiedSheetId]
    ])
];
$batchUpdateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest(['requests' => $requests]);
$res3 = $service->spreadsheets->batchUpdate($destinationSpreadsheetId, $batchUpdateRequest);

Note:

In this sample script, it supposes that your $service = new Google_Service_Sheets($client); can be used for using above script. Please be careful this.

PHP - Google API Copy and Paste row keeping the original format

1 Answers

Modification points:

Sample script:

Note:

References: