Add more 1000 rows to google sheet through API in PHP

Question

I got an append to spreadsheet function, takes data and inserts it into my sheet

 private function _addToSheet($data, $cites)
    {
        $resultList = array_values($data);


        // Path to the credentials JSON file
        $credentialsPath = storage_path('secret.json');

        // Initialize the Google Client
        $client = new Google_Client();
        $client->setApplicationName('Website Scrapping');
        $client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
        $client->setAuthConfig($credentialsPath);
        $client->setAccessType('offline');

        // Create a Sheets service
        $sheetsService = new Google_Service_Sheets($client);

        $spreadsheetId = '******';

        // Get the existing data to determine the next empty row
        $currentData = $sheetsService->spreadsheets_values->get($spreadsheetId, 'Sheet1!A:A');
        if(!$currentData->getValues){
            $currentData->getValues = 0;
        }
        $nextRow = count($currentData->getValues()) + 1;


        // Specify the range for the next empty row
        $range = 'Sheet1!A' . $nextRow . ':M' . $nextRow;

        // Data to update in the Google Sheets
        $time = date('d-m-Y H:i:s');
        array_unshift($resultList, $time);
        $updateBody = new Google_Service_Sheets_ValueRange([
            'values' => [$resultList]
        ]);

        // Perform the update
        $result = $sheetsService->spreadsheets_values->update(
            $spreadsheetId,
            $range,
            $updateBody,
            ['valueInputOption' => 'RAW']
        );
    }

but i get the error: Range (Sheet1!A901:M901) exceeds grid limits. Max rows: 900, max columns: 26

when i visit the gui i find that i have to manually add more columns.

i have many sheets i interact with through the API, its not a solution for me to go and add rows manually, is there a programmatic way to implement using the API in PHP.

add more rows button

i tried this but i get the same error Range (Sheet1!A901:M1900) exceeds grid limits. Max rows: 900, max columns: 26

$currentData = $sheetsService->spreadsheets->get($spreadsheetId);
        $currentRowCount = $currentData->sheets[0]->properties->gridProperties->rowCount;
        
        // Check if the current row count is a multiple of 900
        if ($currentRowCount % 900 === 0) {
            // Increase the row count by 1000
            $dimensionRangeRequest = new Google_Service_Sheets_DimensionRange([
                'sheetId' => $currentData->sheets[0]->properties->sheetId,
                'dimension' => 'ROWS',
                'startIndex' => $currentRowCount-1,
                'endIndex' => $currentRowCount + 1000,
            ]);
            
            $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                'requests' => [$dimensionRangeRequest ],
            ]);
            
            $sheetsService->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        }

oscar dev · Accepted Answer · 2024-03-11 13:47:18Z

1

oh, i had the wrong syntax: here's the correct way to do it.

if ($currentRowCount % 900 === 0) {
   // Increase the row count by 1000
   $dimensionRangeRequest =
      [
      new Google_Service_Sheets_Request([
      'appendDimension' => [
         'sheetId' => 0,
         'dimension' => 'ROWS',
         'length' => 1000,
         ]
      )]
   ];

   $batchUpdateRequest = Google_Service_Sheets_BatchUpdateSpreadsheetRequest(['requests' =>$dimensionRangeRequest]);

   $sheetsService->spreadsheets->batchUpdate($spreadsheetId, 
   $batchUpdateRequest);
}

edited Mar 11, 2024 at 13:47

answered Mar 11, 2024 at 13:46

oscar dev

217 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add more 1000 rows to google sheet through API in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related