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
);

Can you show your current script? And, unfortunately, from `So I need to take some specific rows from the MainSpreadSheet and paste them in the SecondarySpreadSheet using Google API PHP.`, I cannot understand about the detail of your goal. I apologize for this. Also, can you provide the sample input and output situations you expect? — Tanaike, Apr 14 '21 at 00:44
I was using the code similar to this one. But it is not working for me. =>https://stackoverflow.com/questions/59212745/php-google-sheets-api-v4-most-efficient-way-to-get-all-formatting-conditional — Hygison Brandao, Apr 14 '21 at 00:49
Thank you for replying. From `I was using the code similar to this one.`, about your current script, can you add it to your question? And, I have to apologize for my poor English skill. From your replying, unfortunately, from `So I need to take some specific rows from the MainSpreadSheet and paste them in the SecondarySpreadSheet using Google API PHP.`, I cannot still understand about the detail of your goal. I apologize for this. Also, can you provide the sample input and output situations you expect? — Tanaike, Apr 14 '21 at 00:56
Done, thanks. Can you understand how I was trying to remove the $this-> form the code? — Hygison Brandao, Apr 14 '21 at 01:03
The final goal is the main sheet keep the same always and the secondary new sheet will be created with lets say one row from the main sheet: https://docs.google.com/spreadsheets/d/1dW8n3_ypo4_xtLYuaIC36BmBWrGf8ONwzqbuVcX9ahY/edit#gid=0 , for the secondary sheet: https://docs.google.com/spreadsheets/d/1zV2aU9r6-jhE5M51ZhMAp3xAxRYXgfMvpOoeTlxdeWM/edit#gid=0 — Hygison Brandao, Apr 14 '21 at 01:08
Thank you for replying and adding more information. I could confirm your 2 sample Spreadsheets. In your goal, you want to retrieve the specific row from Spreadsheet of "MAIN SHEET" and want to copy the row to the 1st empty row of Spreadsheet of "NEW SHEET". Is my understanding correct? If my understanding is correct, in your actual situation, will you retrieve the multiple rows from "MAIN SHEET"? Or, you want to retrieve only one row every run? — Tanaike, Apr 14 '21 at 01:13
Yes, perfect. Your understanding is correct. But I just need to copy one row to the last empty row and the format/style or the copied row must be the same as the original one — Hygison Brandao, Apr 14 '21 at 01:17
Just one row is enough. because I would sometimes depending one more stuff add more rows or less rows — Hygison Brandao, Apr 14 '21 at 01:19
Thank you for replying. From your additional information, I proposed an answer. Could you please confirm it? If that was not the direction you expect, I apologize. — Tanaike, Apr 14 '21 at 01:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231092/discussion-between-hygison-brandao-and-tanaike). — Hygison Brandao, Apr 14 '21 at 03:16

score 1 · Accepted Answer · answered Apr 14 '21 at 01:55

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.

References:

Just one thing I believe would be better to add. If there is no rows on the sheet the line with `$res2["values"]` will not exist so the $row would not be set and give error. And adding `if(isset($res2["values"])){$row = count($res2["values"]);}else{$row = 0;}` would solve the problem. — Hygison Brandao, Apr 16 '21 at 03:35

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

1 Answers1

Modification points:

Sample script:

Note:

References: