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 minhazur · Accepted Answer · 2019-06-04T19:01:03

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

How to append to a Sheet within a Spreadsheet

1 Answers