CODEWITHSUNDEEP
google-sheetsgoogle-sheets-apigoogle-api-php-client
Hitesh Kumar
Hitesh Kumar

Reputation: 103

Unable to append rows starting with column A everytime

I am trying to append some values in spreadsheet using google api php client.

I have successfully added data in the sheet but every time I run the code, it start appending values from column where the previous ended in the next row.

I am trying to append values in new row every time but it must start from column A.

Here is my code.

$client  = getClient();
$service = new Google_Service_Sheets( $client );

$spreadsheetId = 'my-spreadsheet-id';
$range         = 'sheet1';

$values = array(
    array( "Item", "Cost", "Stocked", '', '', "Ship Date" )
);

$body   = new Google_Service_Sheets_ValueRange( array( 'values' => 
$values, 'majorDimension' => 'ROWS', 'range' => 'sheet1' ) );

$params = array( 'valueInputOption' => 'USER_ENTERED', 
'insertDataOption' => 'INSERT_ROWS' );

$service->spreadsheets_values->append( $spreadsheetId, $range, 
$body, $params );

Now, when I run this code it appends "Item" on column A of a new row and "Ship Date" on column F. This is correct.

But when I run the same code again, it appends the data in new row but starts with Column F and ends with column K.

I want the next time also the data should start appending from column A and ends on column F.

Please help me achieve this, I have read its documentation but couldn't make it work, tried to find answer on internet on various website but was not able to make it work. Can anyone please help me with this.

Upvotes: 3

Views: 1928

Answers (2)

robotsth
robotsth

Reputation: 21

Set the header row of sheet manually and always use the range of the header rows only

$range      = 'sheet1!A1:Z1';

Upvotes: 2

Hitesh Kumar
Hitesh Kumar

Reputation: 103

I found a resolution after going deep inside the code.

If anyone looking for the same then the solution for me is I need to change the range. First get all the values and find the number of rows present in the sheet then you can append blank values in the sheet starting with new row and column A

Here is my updated code for range:

$range      = 'sheet1!A1:Z';
$response   = $service->spreadsheets_values->get( $spreadsheetId, $range );
$get_values = $response->getValues();
$row        = count( $get_values ) + 1;// Plus 1 for new row.

$range = 'sheet1!A' . $row . ':Z' . $row;

Use this range in the code which works fine.

Upvotes: 2

Related Questions