findFirstEmptyRow("企業專區詢問!A:B");
// echo "第一個空白列 Row Index:" .$emptyRow;
// echo "
";
//write data
$R = $emptyRow;
$range = "企業專區詢問!A$R:M$R";
// $values = [["Data 1", "Data 2", "Data 3", "Data 4"]];
$result = $assistant->writeData($range, $values);
echo "寫入格數" .$result;
echo "
";
}catch (Exception $e) {
}
die('
END');
require_once 'vendor/autoload.php';
$service_account_key="/data/www/google_service_account_key__+-?e1/aia-courses-d87ec0f4cbdf.json";
$spreadsheetId = '1pnnP96lXXs_Q3jgWf4Ye697CquSKdzlbPEhtfm-Igb4';
// Set up the Google API Client
$client = new Google_Client();
$client->setAuthConfig($service_account_key);
$client->addScope(Google_Service_Sheets::SPREADSHEETS_READONLY);
$client->addScope(Google_Service_Sheets::SPREADSHEETS);
// Create the service object for the Google Sheets API
$service = new Google_Service_Sheets($client);
#### Read ####
$range = 'testing!A:B';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
echo "";
print_r($values);
echo "
";
#### Write ####
// ID of the spreadsheet and the range where data will be written
$spreadsheetId = '1pnnP96lXXs_Q3jgWf4Ye697CquSKdzlbPEhtfm-Igb4';
$range = 'testing!A5:D5'; // Modify the sheet name and cell range as needed
// Prepare values to be written
// This example writes one row of data. Extend the outer array for more rows.
$values = [
["Data 1", "Data 2", "Data 3", "Data 4"]
];
// Prepare the request body and set major dimension and values
$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);
// Specify how the input data should be interpreted
$params = [
'valueInputOption' => 'RAW' // Or 'USER_ENTERED'
];
// Update the spreadsheet
$response = $service->spreadsheets_values->update(
$spreadsheetId,
$range,
$body,
$params
);
// Check the response to confirm the data write
if ($response instanceof Google_Service_Sheets_UpdateValuesResponse) {
echo 'Data written successfully to range: ' . $response->getUpdatedRange() . "\n";
echo 'Total cells updated: ' . $response->getUpdatedCells();
} else {
echo 'Failed to write data.';
}
#### Find the first empty row ####
// ID of the spreadsheet and the range where you want to check for the empty row
$spreadsheetId = '1pnnP96lXXs_Q3jgWf4Ye697CquSKdzlbPEhtfm-Igb4';
$range = 'testing!A:A'; // Check column A, adjust as needed
// Request to get the values from the specified range
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
// Find the first empty row
$emptyRow = null;
if (empty($values)) {
echo 'The entire column is empty.';
} else {
foreach ($values as $index => $row) {
if (empty($row) || (isset($row[0]) && $row[0] === '')) {
$emptyRow = $index + 1; // Adding 1 because array index starts at 0
break;
}
}
if (is_null($emptyRow)) {
echo 'No empty rows found within the range.';
} else {
echo "The first empty row is: Row " . $emptyRow;
}
}
?>