# GOOGLE SHEET API

官方範例 [Quickstart for Sheets API with the PHP Client](https://developers.google.com/sheets/api/quickstart/php) 是使用 OAuth 認證，如果單純用程式處理 google sheet 沒有使用者參與的情形，可以參考以下方式

## GCP 設定

1. 至 GCP APIs & Services > Library 啟用 Google Sheets API
2. 新增 Credentials，選擇 Service account 類型
3. 產生該 Service account 的 private key，選擇 JSON 類型，保存好這個檔案之後會需要使用

## 安裝 Google APIs Client Library

composer require google/apiclient:"^2.0"

## 範例
### connection

`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;
}

### read

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

### create

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);
}   

### update

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']);
}

### clear sheet

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)
}

###### tags: `trick`