Google Sheets API Insert Row at a Row Position

Question

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

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

Google Sheets API Insert Row at a Row Position

1 Answers

You are using values.append which by definition appends values to the first empty row