PHP Google Sheets API v4 Most efficient way to get all formatting - conditional, dates, etc - from preceeding row and apply to a new row insert?

Question

Good day folks!

What is the best way to insert a new row of data and apply the styling from the preceding row? (We are migrating our PHP code to use the Google Sheets api v4 classes)

I am currently working my way through this and using Google_Service_Sheets_BatchUpdateSpreadsheetRequest against the spreadsheets->batchUpdate I can apply format changes in code. However I'm not too sure on the appropriate get request which I could cycle through in order to pick up the existing formats and use these?

In old money we didn't have to worry about this at all as we just added the new row's data against the listFeed->insert function which seemed to take care of the formatting side itself.

However the question now is do I need to cycle through every cell within a range getting the formatting and then applying that a cell at a time to the new row or is there a better way (e.g. an existing row level operation)?

I have to apologize for my poor English skill. I cannot understand about `insert a new row of data and apply the styling from the preceding row`. `insert a new row` is the same with appending values to the next row of the last row in the sheet? Or it's other situation? And where is `the preceding row`? So in order to correctly what you want, can you provide your current script and explain about your goal? Of course, please remove your personal information. — Tanaike, Dec 06 '19 at 22:35
Yes. "insert a new row" means appending values below the last filled row in the sheet. Unfortunately I cannot provide a current script which would be of any value as this is a migration issue. As I was saying in the question above, the current code uses listFeed->insert with the list feed being a row based object derived from a given sheet. There is no php api v4 equivalent (as far as I can see). — Mr Davros, Dec 10 '19 at 12:54
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't understand about your goal. — Tanaike, Dec 10 '19 at 22:32
No worries. Just for the interest of anyone else landing on this page, it would appear that what I was looking to do isn't even possible from the current api. The answer given in this thread:-https://stackoverflow.com/questions/58843013/get-cell-format-information-from-google-sheets-using-php says in particular "You're able to change the cells format using UpdateCells request ... **but you can't retrieve it using Sheets API**." I tested the api on Google's own page here:- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get and no cell format data is returned. — Mr Davros, Dec 11 '19 at 09:34
As far as I can see this means there is no possible way to access and cycle through all the formatting applied to one particular cell and apply it to another. — Mr Davros, Dec 11 '19 at 09:37
Thank you for replying. In your goal, you want to append a data to the next row of the last row of the Spreadsheet. At this time, you want to copy the format of the last row to the appended row. You want to achieve this using google-api-php-client with php. I could understand like this. Can I ask you whether my understanding is correct? — Tanaike, Dec 11 '19 at 12:14

Mr Davros · Accepted Answer · 2019-12-12T12:51:52.677

I wouldn't normally answer my own question but given that Google are closing off the v3 Sheets API in March 2020 I suspect others may come across this problem.

As I have mentioned in comments against the original question there is no existing means in the v4 API of accessing Cell Formats. Therefore there is no option to loop through one range and apply formats to another.

My workaround solution is to copy / paste the preceding row (the default PASTE_NORMAL will copy all values, formulas, formats, and merges https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#pastetype) and then overwrite the newly pasted row with the required values. This gives me the required values with the existing formatting in the new row:-

    $sheetId = null;
    $worksheetSheets = $this->spreadsheetService->spreadsheets->get($this->spreadsheetId)->sheets;
    foreach($worksheetSheets as $sheet){
        $sheetTitle = $sheet->properties['title'];
        if ($sheetTitle === $this->worksheetTitle){
            $sheetId = $sheet->properties['sheetId'];     
            break;
        }
    }

    $copyRange = new Google_Service_Sheets_GridRange();
    $copyRange->setSheetId($sheetId);
    $copyRange->setStartRowIndex($nextRow - 2);
    $copyRange->setEndRowIndex($nextRow - 1);
    $copyRange->setStartColumnIndex(0);
    $copyRange->setEndColumnIndex(400);

    $pasteRange = new Google_Service_Sheets_GridRange();
    $pasteRange->setSheetId($sheetId);
    $pasteRange->setStartRowIndex($nextRow - 1);
    $pasteRange->setEndRowIndex($nextRow);
    $pasteRange->setStartColumnIndex(0);
    $pasteRange->setEndColumnIndex(400);

    $copypasteRequest = new Google_Service_Sheets_CopyPasteRequest();

    $copypasteRequest->setSource($copyRange);
    $copypasteRequest->setDestination($pasteRange);
    //$copypasteRequest->pasteType(CopyPasteType.PASTE_NORMAL);

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

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

    // Need to check if sheet has an existing empty row to paste into
    $finalRowRange = $this->worksheet['range'];
    $finalRowStartPosition = strpos($this->worksheet['range'],':') + 2;
    $finalRow = intval(substr($finalRowRange,$finalRowStartPosition));

    if($finalRow <= $pasteRange['startRowIndex']){ // startRowIndex is a zero based array range, i.e. 348 actually corresponds to row 349 on sheet.
        $appendDimensionRequest = new Google_Service_Sheets_AppendDimensionRequest();
        $appendDimensionRequest->setSheetId($sheetId);
        $appendDimensionRequest->setDimension("ROWS");
        $appendDimensionRequest->setLength(1);
        $appendRequest = new Google_Service_Sheets_Request(); 
        $appendRequest->setAppendDimension($appendDimensionRequest);
        $appendEmptyRowBatchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
        $appendEmptyRowBatchUpdateRequest->setRequests($appendRequest);
         $this->spreadsheetService->spreadsheets->batchUpdate($this->spreadsheetId, $appendEmptyRowBatchUpdateRequest);
    }
$this->spreadsheetService->spreadsheets->batchUpdate($this->spreadsheetId, $batchUpdateRequest);

    // The actual data values insert
    $this->spreadsheetService->spreadsheets_values->update(
       $this->spreadsheetId,
       $updateRange,
       $valueRange,
       $conf
    );

I hope this may be of some use to someone in future.

It is a bit confusing how all the classes work, is it possible if you write the google classes used for the $this->spreadsheetService, $this->worksheet['range'] and also the value for the variables $updateRange, $valueRange, $conf./ Thanks — Hygison Brandao, Apr 13 '21 at 14:52

PHP Google Sheets API v4 Most efficient way to get all formatting - conditional, dates, etc - from preceeding row and apply to a new row insert?

1 Answers1

Linked