I am using the Google API to add rows to a Google Sheet. This is all working well, but the new rows are being added to the end (using the append method).
I would like to insert the row at the top, below the heading, in my case Row 2.
This is my working code for adding to the end. How can I specify where I want to insert the row at a given position.
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $CredentialFile); $client = new Google_Client; $client->useApplicationDefaultCredentials(); $client->setApplicationName("MyApp"); $client->setScopes(['https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds']); if ($client->isAccessTokenExpired()) { $client->refreshTokenWithAssertion(); } $accessToken = $client->fetchAccessTokenWithAssertion()["access_token"]; ServiceRequestFactory::setInstance(new DefaultServiceRequest($accessToken)); $SheetId = "MySheetId"; $service = new Google_Service_Sheets($client); $sheet = $service->spreadsheets->get($SheetId); $sheets = $sheet->getSheets(); $SheetTitle = $sheets[0]->properties->title; $range = "{$SheetTitle}!A:D"; $values = []; $row = []; $row[] = "Tom"; $row[] = "Thumb"; $row[] = "tomthumb"; $row[] = "tom@thumb.com" $values[] = $row; $body = new Google_Service_Sheets_ValueRange([ 'values' => $values ]); $params = [ 'valueInputOption' => "RAW" ]; $result = $service->spreadsheets_values->append($SheetId, $range, $body, $params);
Advertisement
Answer
You are using values.append which by definition appends values to the first empty row
If you want to insert your values in a range that is not the first empty row of the sheet, you need to use instead the method values.batchUpdate
Sample:
$range="Sheet1!A2:D2"; $values = array( array( "Tom", "Thumb", "tomthumb", "tom@thumb.com" ) ); $data = array(); $data[] = new Google_Service_Sheets_ValueRange(array( 'range' => $range, 'values' => $values ) ); $body = new Google_Service_Sheets_BatchUpdateValuesRequest(array( 'valueInputOption' => 'RAW', 'data' => $data ) ); $result = $service->spreadsheets_values->batchUpdate($SheetId, $body);
UPDATE
- I you want to insert a row with with data rather than pasting data into an already existing row – you need to create the additional row first.
This can be done with a InsertDimensionRequest.
Sample:
$request = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array( 'requests' => array( 'insertDimension' => array( 'range' => array( "startIndex": 1, "endIndex": 2, "dimension": "ROWS", "sheetId": 0 ) ) ) )); $request->setRequests($body); $result = $service->spreadsheets->batchUpdate($SheetId, $request);