Google sheets API range issue

I have a google sheet like this. Those values are just place holders at the moment for my headers

When I run my PHP script with $range = ‘Sheet!F2:L2’; to insert records into the google sheet via API/service account and I only want to start inserting at ROW F for all attempts, it inserts fine on the 1st attempt but all following insertions which then inserts into row 3 and onwards, inserts the data starting at Column 1/A?

Only when I set my range to F3:L3 and leave row 2 blank does it insert concurrently into the correct columns?

Code:

require '/var/www/html/app/vendor/autoload.php';
$client = new Google_Client();
$client->setApplicationName('My PHP App');
$client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig('/var/www/html/clients-api/credentials.json');
$sheets = new Google_Service_Sheets($client);
$spreadsheetId = '1bo123c1UtdWKV25_312znDO123dDp631238D5qN1123lQ';
$range = 'Sheet1!F2:L2';
$values = [
    [$data->FirstName,$data->LastName,$data->ID,"","",$data->PrimaryNumber,$data->PrimaryEmail]
];
$body = new Google_Service_Sheets_ValueRange([
    'range' => $range,
    'values' => $values
]);
$params = [
    'insertDataOption' => 'INSERT_ROWS',
    'valueInputOption' => "RAW"
];
$result = $sheets->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

echo '<pre>', var_export($result, true), '</pre>', "n";

Answer

Issue:

As you can see in the official docs:

The input range is used to search for existing data and find a “table” within that range.

The data will be correspondingly appended after this “table”, starting at the first column of it.

In this case, the first time the data is appended, F2:L2 doesn’t belong to any existing table, since all those cells are empty.

That’s not the case in successive times, in which F2:L2, already populated, is considered to be part of a table containing the headers in the first row, since there are no blank cells separating F2:L2 from A1:L1. The table that is found, then, when trying to add data to F3:L3, corresponds to A1:L2. And so it is appending the data starting at column A, since that’s where the table starts.

You can check that’s the case by examining the tableRange from the API response. In your case, when trying to append F3:L3, it will be something like "tableRange": "Sheet1!A1:L2".

Solutions:

• One option would be to make the input range refer to row 1 (e.g. A1) from the beginning, when appending F2:L2, and add empty values at the start of the array values:

$range = 'Sheet1!A1';
$values = [
    ["","","","","",$data->FirstName,$data->LastName,$data->ID,"","",$data->PrimaryNumber,$data->PrimaryEmail]
];

• Another alternative would be to update the input range every time, in order to specify a range that doesn’t belong to a “table” (that is to say, successively F2:L2, F3:L3, F4:L4, etc.).

Reference:

• Appending values