mercredi 28 octobre 2020

Get the first free column in a Google Sheet via PHP API

I'm using the Google Sheets API to write some data into the sheets, but so far either I clear it all and write everything again or I write in new rows (which Sheets API does by default).

I am now writing a single column per run, but I need to get the first available column in the sheet, so I can pass it as the range of writing.

This is my code so far:

        $sheet = new \Google_Service_Sheets($this->client);
        $range = "'" . $sheetName . "'!" . $rangeArg . (strlen($rangeArg) == 2 ? '' : count($data) + 1000);

        $response = $sheet->spreadsheets_values->get($this->sheetId, $range);

        if (!$clear && $response && $response->values) {
            $c = count($response->values);
            $newRange = (intval(substr($rangeArg, 1, 1)) + $c);
            $newRange = substr($rangeArg, 0, 1) . $newRange . substr($rangeArg, 2);
            $range = "'" . $sheetName . "'!" . $newRange . (count($data) + 1000);
        }

        $options = ['valueInputOption' => 'RAW'];

        if ($clear) {
            $sheet->spreadsheets_values->clear($this->sheetId, $range, new \Google_Service_Sheets_ClearValuesRequest);
        }

       $body = new \Google_Service_Sheets_ValueRange(['values' => $data, 'majorDimension' => $columns ? 'COLUMNS' : 'ROWS']);
       $ok = $sheet->spreadsheets_values->append($this->sheetId, $range, $body, $options);
    }

I saw someone on the internet mentioning getLastColumn() as a function, but it's not available in my version of sheets API apparently or it's just not in this package.

google/apiclient                   v2.5.0
google/apiclient-services          v0.138
google/auth                        v1.9.0


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire