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
– Tanaike, Commented Apr 14, 2021 at 0:44
I was using the code similar to this one. But it is not working for me. =>stackoverflow.com/questions/59212745/… — Hygison Brandao
– Hygison Brandao, Commented Apr 14, 2021 at 0: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
– Tanaike, Commented Apr 14, 2021 at 0:56
Done, thanks. Can you understand how I was trying to remove the $this-> form the code? — Hygison Brandao
– Hygison Brandao, Commented Apr 14, 2021 at 1:03
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
– Tanaike, Commented Apr 14, 2021 at 1:13

Tanaike · Accepted Answer · 2021-04-14 01:55:12Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Sample script:

Note:

References:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Sample script:

Note:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related