How to append to a Sheet within a Spreadsheet

Question

Looking at the Google Sheets API it seems easy enough to append rows to an existing Spreadsheet (documented here).

What I can't work out how to do is append a row to a specific Sheet within a Spreadsheet containing multiple Sheets. Sheets appear to have their own ID (documented here) but the append action calls for a spreadsheetID. Appending to a Spreadsheet containing multiple Sheets seems to always append to whatever the "first" Sheet in the Spreadsheet currently is.

Does anyone know how to make a request with the API/SDK to append a row to a specific Sheet?

minhazur · Accepted Answer · 2022-08-16 10:36:29Z

To append rows or data into a sheet, you need to use sheet name, not the sheet id.

Example:

Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

Please, follow these two links

A1 Notation

Append values to sheet

Code sample in PHP

$googleClient = createGoogleClient(); //your implementation to get google client $sheetService = new Google_Service_Sheets($googleClient); $spreedSheetId = "1pz5uJAUnk3BRR1dxWz_C5NHhMpf20yPQnLYIoyl0kdd"; //your spreedsheet id $sheetTitle = "Sheet1"; //SheetTitle $range = "'" . $sheetTitle . "'!A1"; $row = array("Column1_Value", "Column2_Value", "Column3_Value", "Column4_Value"); $rows[] = $row; /** @var Google_Service_Sheets_ValueRange $body */ $body = new Google_Service_Sheets_ValueRange(['values' => $rows]); $params = [ 'valueInputOption' => 'USER_ENTERED', 'insertDataOption' => 'OVERWRITE', 'responseValueRenderOption' => 'FORMATTED_VALUE', ]; $result = $sheetService->spreadsheets_values->append($spreedSheetId, $range, $body, $params); $updatedCells = $result->getUpdates()->getUpdatedCells(); printf("%d cells updated.", $updatedCells);

So would this notation be passed as the range path parameter mentioned here? If that's the case would be able to update the answer to make that a little clearer?
you got it right. Let me add a bit of detail in the answer section. Meanwhile, you can check these links. github.com/gsuitedevs/php-samples developers.google.com/sheets/api/samples/writing

Collectives™ on Stack Overflow

How to append to a Sheet within a Spreadsheet

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related