Add multiple rows to Google Spreadsheet via API

Question

I can add a new row to a Google spreadsheet with this code:

$valueRange = new Google_Service_Sheets_ValueRange(); $valueRange->setValues(["values" => ['data1', 'data2']]); $range = 'Sheet1!A1:A'; $conf = ["valueInputOption" => "USER_ENTERED"]; $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);

How should I change the setValues params to add multiple rows?

Thank you.

UPDATE

In the meantime I use this approach:

$range_number = 1; // Keep spreadsheet header $data = array(); for($i=0;$i<count($data);$i++){ $range_number++; $range = 'Sheet1!A'.$range_number.':XX'; $values = array( array($data1[$i], $data2[$i], $data3[$i]) ); $data[] = new Google_Service_Sheets_ValueRange( array( 'range' => $range, 'values' => $values ) ); } $body = new Google_Service_Sheets_BatchUpdateValuesRequest(array( 'valueInputOption' => "USER_ENTERED", 'data' => $data )); $result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body); // Clear rest of spreadsheet $range_number++; $range = 'Sheet1!A'.$range_number.':XX'; $clearRange = new Google_Service_Sheets_ClearValuesRequest(); $service->spreadsheets_values->clear($spreadsheetId, $range, $clearRange);

But this way I have to send the previous data as well. My goal would be just to append the new data.

Karakuchi · Accepted Answer · 2017-11-13 22:41:15Z

For those of you who want to append multiple rows instead of update rows. You can use this solution.

$valueRange = new Google_Service_Sheets_ValueRange(); $valueRange->setValues([['John', 'Doe'],['Jane', 'Doe']]); $range = 'Sheet1!A1:A'; $conf = ["valueInputOption" => "USER_ENTERED"]; $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);

It took me ages to work this out hope it helps.

ReyAnthonyRenacia · Accepted Answer · 2017-03-12 23:58:06Z

2

Try to follow the Insert an empty row or column guide. Indicate "ROWS" as your "dimension" and add how many rows you want by using startIndex and endIndex.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate { "requests": [ { "insertDimension": { "range": { "sheetId": sheetId, "dimension": "ROWS", "startIndex": 0, "endIndex": 100 }, "inheritBefore": false } }, ], }

answered Mar 12, 2017 at 23:58

ReyAnthonyRenacia

17.7k6 gold badges42 silver badges63 bronze badges

5 Comments

Zoltan Over a year ago

Thanks. I can add multiple rows by batchUpdate, but with this method I have to set the rows I want to add data. Is there a way to append multiple rows, even if I don't know which row has the last data?

ReyAnthonyRenacia Over a year ago

Have you tried spreadsheets.values.append? as of now this is the append-related methond in SheetsV4.

Zoltan Over a year ago

Yes. But I can't add more then one row with append

ReyAnthonyRenacia Over a year ago

If you check the sample on this Append Values guide it demonstrates adding more than one row using that method.

Zoltan Over a year ago

Thanks, it's working :) I needed to add the majorDimension attribute.

Evgeni Pavlov · Accepted Answer · 2024-02-07 15:46:09Z

You need an array like this in php, for example

[ ['value1','value2','value3'], ['value4','value5','value6'] ]

To do so, if you have multiple array like

[ [0][0]=>'value1',[1]=>'value2',[2]=>'value3', [1][0]=>'value4',[1]=>'value5',[2]=>'value6' ]

You need to iterate subArrays

$values = []; $i = 0; foreach ($yourArr as $subArray): foreach ($subArray as $key => $value): $values[$i][$key] = $value; endforeach; $i++; endforeach;

And then

$body = new Google_Service_Sheets_ValueRange([ 'values' => $values ]);

Collectives™ on Stack Overflow

Add multiple rows to Google Spreadsheet via API

3 Answers 3

Comments

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Linked

Related