client = new Google_Client();
$this->client->setAuthConfig($this->service_account_key_path);
$this->client->addScope(Google_Service_Sheets::SPREADSHEETS_READONLY);
$this->client->addScope(Google_Service_Sheets::SPREADSHEETS);
// Create the service object for the Google Sheets API
$this->service = new Google_Service_Sheets($this->client);
$this->spreadsheetId = $spreadsheetId;
}
/**
* Method to write data to a sheet
* @param string $range 'testing!A3:D3';
* @param array $values [["Data 1", "Data 2", "Data 3", "Data 4"]];
*/
public function writeData($range, $values, $valueInputOption = 'USER_ENTERED') {
$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);
$params = [
'valueInputOption' => $valueInputOption
];
$response = $this->service->spreadsheets_values->update(
$this->spreadsheetId,
$range,
$body,
$params
);
$updated_cells_count = 0;
// Check the response to confirm the data write
if ($response instanceof Google_Service_Sheets_UpdateValuesResponse) {
// $result = 'Data written successfully to range: ' . $response->getUpdatedRange() . "
";
// $result.= 'Total cells updated: ' . $response->getUpdatedCells();
$updated_cells_count = $response->getUpdatedCells();
}
return $updated_cells_count;
}
/**
* Method to write data to a sheet
* @param string $range : 'testing!A3:D3';
*/
public function readData($range) {
$response = $this->service->spreadsheets_values->get($this->spreadsheetId, $range);
$values = $response->getValues();
return $values;
}
public function findFirstEmptyRow($range) {
// Request to get the values from the specified range
$response = $this->service->spreadsheets_values->get($this->spreadsheetId, $range);
$values = $response->getValues();
// echo "";print_r($values);echo "
";
// Find the first empty row
$emptyRow = null;
if (empty($values)) {
$emptyRow = 1;
} else {
foreach ($values as $index => $row) {
$emptyRow = $index + 1; // Adding 1 because array index starts at 0
if (empty($row) || (isset($row) && implode("", $row) === '')) {
return $emptyRow;
}
}
}
return $emptyRow + 1; //mean there are not empty row in $response's return range;
}
}
if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME']) && $_SERVER['REMOTE_ADDR'] == '61.222.27.93') {
$spreadsheetId = '1pnnP96lXXs_Q3jgWf4Ye697CquSKdzlbPEhtfm-Igb4';
$assistant = new GoogleSheetsAssistant($service_account_key, $spreadsheetId);
//write data
$range = 'testing!A3:D3';
$values = [["Data 1", "Data 2", "Data 3", "Data 4"]];
$result = $assistant->writeData($range, $values);
echo "寫入格數" . $result;
echo "
";
//read data
$values = $assistant->readData('testing!A3:D3');
echo "讀取資料:";
print_r($values);
echo "";
echo "
";
//find first empty
$emptyRow = $assistant->findFirstEmptyRow("testing!A:B");
echo "第一個空白列 Row Index:" . $emptyRow;
}