I'm using google sheet api with this logic for each request:
- create a sheet,
- add custom query there (custom formula),
- fecth query results,
- delete the created sheet.
The creation code is a follow with Laravel:
class GoogleSheetsApiClientManager {
private $client;//authorized API client.
private $service = null;
public function __construct($apiOptions) {
$this->client = new Google_Client();
$this->client->setApplicationName(array_get($apiOptions, 'application_name', 'Google Sheets API PHP'));
$this->client->setScopes(array_get($apiOptions, 'scope', Google_Service_Sheets::SPREADSHEETS));
$this->client->setAuthConfig(array_get($apiOptions, 'credential_path'));
$this->client->setAccessType('offline');
$this->service = new Google_Service_Sheets($this->client);
}
public function addNewSheet($spreadsheetId, $hidden = false, $sheetName = false){
try {
$properties = [
'hidden' => $hidden
];
if($sheetName){
$properties['title'] = $sheetName;
}
$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
'requests' => [
'addSheet' => [
'properties' => $properties
]
]
]);
$result = $this->service->spreadsheets->batchUpdate($spreadsheetId, $body);
return $result;
}
catch (\Exception $exception) {
Log::error('Add new sheet using Google Sheet API : '.$exception->getMessage());
return false;
}
}
}
And the call is like:
$apiOptions = [
'application_name' => 'My app name',
'credential_path' => __DIR__.'/service_account-secret.json'
];
$googleSheetManager = new GoogleSheetsApiClientManager($apiOptions);
$spreadsheetId = '--theSpreasheetId--';
$mySheet = $googleSheetManager->addNewSheet($spreadsheetId);
The issue : Each time I create new sheet, its name is based on last created one. eg: last sheet name is sheet5, even sheet5 has been deleted I got new sheet with name sheet6.
What I would like to have is the minimal possible name, meaning if sheet5 & sheet4 are not there (but sheet3 is present), I would like a new name sheet4, not an endless incremented name
