0

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

6
  • 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. Commented Dec 6, 2019 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). Commented Dec 10, 2019 at 12:54
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't understand about your goal. Commented Dec 10, 2019 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:-stackoverflow.com/questions/58843013/… 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:- developers.google.com/sheets/api/reference/rest/v4/spreadsheets/… and no cell format data is returned. Commented Dec 11, 2019 at 9: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. Commented Dec 11, 2019 at 9:37

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.