官方範例 Quickstart for Sheets API with the PHP Client 是使用 OAuth 認證,如果單純用程式處理 google sheet 沒有使用者參與的情形,可以參考以下方式
composer require google/apiclient:"^2.0"
gcs_civil.json
就是之前保留的檔案
function getClient()
{
$client = new \Google_Client();
$client->setApplicationName('Qucikstart');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS_READONLY]);
$client->setAccessType('offline');
$jsonAuth = file_get_contents(__DIR__ . "/../../../config/gcs_civil.json");
$client->setAuthConfig(json_decode($jsonAuth, true));
return $client;
}
function get($client, $spreadSheetId, $range): array
{
$service = new \Google_Service_Sheets($client);
$response = $this->service->spreadsheets_values->get($spreadSheetId, $range);
return $response->getValues();
}
$client = getClient();
$datas = get($client, 'sheet_id', 'tab_name!A2:E')
補充,如果儲存格只到 B 欄有資料,那陣列會是 2 (只有 A, B),長度並非固定是 5
function create($client) {
$service = new \Google_Service_Sheets($client);
$spreadsheet = new \Google_Service_Sheets_Spreadsheet([
'properties' => [
'title' => 'test-sheet'
]
]);
$spreadsheet = $service->spreadsheets->create($spreadsheet, [
'fields' => 'spreadsheetId'
]);
printf("Spreadsheet ID: %s\n", $spreadsheet->spreadsheetId);
}
function update($client, $spreadSheetId) {
$service = new \Google_Service_Sheets($client);
$values = [
['col-a', 'col-b', 'col-c'],
// other row
];
$tab = 'tab-name';
$rowCount = count($values);
$range = "'{$tab}'!A1:C{$rowCount}";
$updateBody = new \Google_Service_Sheets_ValueRange([
'range' => $range,
'majorDimension' => 'ROWS',
'values' => $values,
]);
$result = $service->spreadsheets_values->update($spreadSheetId, $range, $updateBody, ['valueInputOption' => 'raw']);
}
function clear($client, $spreadSheetId) {
$service = new \Google_Service_Sheets($client);
$range = "'test'!A1:C";
$requestBody = new \Google_Service_Sheets_ClearValuesRequest();
$response = $service->spreadsheets_values->clear($spreadSheetId, $range, $requestBody)
}
trick