When i run below command from my project directory i got below error:
Error:
Google_Service_Exception { "error": { "code": 429, "message": "Quota exceeded for quota group 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:797591429926'.", "errors": [ { "message": "Quota exceeded for quota group 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:797591429926'.", "domain": "global", "reason": "rateLimitExceeded" } ], "status": "RESOURCE_EXHAUSTED" } }
Command File:
app/Console/Commands/SaveDataToGoogleSheet.php
public function handle()
{
$users = User::whereHas('roles', function ($q) {
$q->where('name', Role::ROLE_ENGINEER);
})->with(['location', 'experience', 'workAreas', 'gender', 'national', 'currentPosition', 'currentContract',
'requiredPositions', 'requiredContracts', 'requiredWorkAreas', 'languages', 'skills', 'expertise',
'jobSeekingSituation', 'incomeType', 'engineerPastExperiences'])
->get();
$range = env('GOOGLE_SHEET_NAME').'!A2:A'; // get 'USER ID' Column
$spreadsheetId = env('GOOGLE_SHEET_ID');
$service = $this->authToGoogleSheet();
Log::info($spreadsheetId);
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
$valueRange= new \Google_Service_Sheets_ValueRange();
if (empty($values)) { // if sheet is empty
foreach ($users as $user) {
$total = 0;
$technicalTotal = 0;
$otherTotal = 0;
foreach ($user->skills as $skill) {
$score = Score::where('item_id', $skill->item_id)->where('level_id', $skill->level_id)->first();
if ($score) {
$level = SubcategoryLevel::find($skill->level_id);
$total = $total + $score->score;
if (Subcategory::whereHas('category', function ($query) {
$query->where('name', 'Technical');
})->find($level->subcategory_id)) {
$technicalTotal = $technicalTotal + $score->score;
} else
$otherTotal = $otherTotal + $score->score;
}
}
$valueRange->setValues(["values" => [
$user->id,
$user->status,
$user->test_user ? true : false,
$user->verified ? true : false,
$user->interview_completed ? true : false,
$user->last_login_time ?: '',
$user->registered_by_admin ? true : false,
$user->first_name,
$user->last_name,
$user->email,
$user->nickname ?: '',
$user->birthdate ?: '',
$user->birthdate ? Carbon::parse($user->birthdate)->age : '',
$user->gender ? $user->gender->jp_gender : '',
$user->jobSeekingSituation ? $user->jobSeekingSituation->jp_situation : '',
$user->expertise ? $user->expertise->jp_expertise : '',
$user->career_summary ?: '',
$user->location ? $user->location->jp_location : '',
$user->national ? $user->national->jp_national : '',
$user->languages ? implode($user->languages->pluck('jp_language')->toArray(), ',') : '',
$user->requiredPositions ? implode($user->requiredPositions->pluck('jp_required_position')->toArray(), ',') : '',
$user->requiredContracts ? implode($user->requiredContracts->pluck('jp_required_type')->toArray(), ',') : '',
$user->requiredWorkAreas ? implode($user->requiredWorkAreas->pluck('jp_work_area')->toArray(), ',') : '',
$user->income ? number_format($user->income) : '',
$user->incomeType ? $user->incomeType->jp_type : '',
$user->policy ?: '',
$user->experience ? $user->experience->jp_experience : '',
$user->currentPosition ? $user->currentPosition->jp_required_position : '',
$user->currentContract ? $user->currentContract->jp_required_type : '',
$total,
$technicalTotal,
$otherTotal,
$user->manager_comments ?: ''
]]);
$conf = ["valueInputOption" => "RAW"];
$ins = ["insertDataOption" => "INSERT_ROWS"];
$service->spreadsheets_values->append($spreadsheetId, env('GOOGLE_SHEET_NAME'), $valueRange, $conf, $ins);
}
} else {
$userIdsFromSheet = [];
foreach ($values as $row) {
array_push($userIdsFromSheet, $row[0]);
}
foreach ($users as $user) {
$total = 0;
$technicalTotal = 0;
$otherTotal = 0;
foreach ($user->skills as $skill) {
$score = Score::where('item_id', $skill->item_id)->where('level_id', $skill->level_id)->first();
if ($score) {
$level = SubcategoryLevel::find($skill->level_id);
$total = $total + $score->score;
if (Subcategory::whereHas('category', function ($query) {
$query->where('name', 'Technical');
})->find($level->subcategory_id)) {
$technicalTotal = $technicalTotal + $score->score;
} else
$otherTotal = $otherTotal + $score->score;
}
}
if (in_array($user->id, $userIdsFromSheet)) {
$rowNo = array_search($user->id, $userIdsFromSheet) + 2;
$data[] = new \Google_Service_Sheets_ValueRange([
'values' => [[
$user->id,
$user->status,
$user->test_user ? true : false,
$user->verified ? true : false,
$user->interview_completed ? true : false,
$user->last_login_time ?: '',
$user->registered_by_admin ? true : false,
$user->first_name,
$user->last_name,
$user->email,
$user->nickname ?: '',
$user->birthdate ?: '',
$user->birthdate ? Carbon::parse($user->birthdate)->age : '',
$user->gender ? $user->gender->jp_gender : '',
$user->jobSeekingSituation ? $user->jobSeekingSituation->jp_situation : '',
$user->expertise ? $user->expertise->jp_expertise : '',
$user->career_summary ?: '',
$user->location ? $user->location->jp_location : '',
$user->national ? $user->national->jp_national : '',
$user->languages ? implode($user->languages->pluck('jp_language')->toArray(), ',') : '',
$user->requiredPositions ? implode($user->requiredPositions->pluck('jp_required_position')->toArray(), ',') : '',
$user->requiredContracts ? implode($user->requiredContracts->pluck('jp_required_type')->toArray(), ',') : '',
$user->requiredWorkAreas ? implode($user->requiredWorkAreas->pluck('jp_work_area')->toArray(), ',') : '',
$user->income ? number_format($user->income) : '',
$user->incomeType ? $user->incomeType->jp_type : '',
$user->policy ?: '',
$user->experience ? $user->experience->jp_experience : '',
$user->currentPosition ? $user->currentPosition->jp_required_position : '',
$user->currentContract ? $user->currentContract->jp_required_type : '',
$total,
$technicalTotal,
$otherTotal,
$user->manager_comments ?: ''
]],
'range' => env('GOOGLE_SHEET_NAME').'!'.$rowNo.':'.$rowNo
]);
$body = new \Google_Service_Sheets_BatchUpdateValuesRequest([
'valueInputOption' => 'USER_ENTERED',
'data' => $data
]);
$service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
} else {
$valueRange->setValues(["values" => [
$user->id,
$user->status,
$user->test_user ? true : false,
$user->verified ? true : false,
$user->interview_completed ? true : false,
$user->last_login_time ?: '',
$user->registered_by_admin ? true : false,
$user->first_name,
$user->last_name,
$user->email,
$user->nickname ?: '',
$user->birthdate ?: '',
$user->birthdate ? Carbon::parse($user->birthdate)->age : '',
$user->gender ? $user->gender->jp_gender : '',
$user->jobSeekingSituation ? $user->jobSeekingSituation->jp_situation : '',
$user->expertise ? $user->expertise->jp_expertise : '',
$user->career_summary ?: '',
$user->location ? $user->location->jp_location : '',
$user->national ? $user->national->jp_national : '',
$user->languages ? implode($user->languages->pluck('jp_language')->toArray(), ',') : '',
$user->requiredPositions ? implode($user->requiredPositions->pluck('jp_required_position')->toArray(), ',') : '',
$user->requiredContracts ? implode($user->requiredContracts->pluck('jp_required_type')->toArray(), ',') : '',
$user->requiredWorkAreas ? implode($user->requiredWorkAreas->pluck('jp_work_area')->toArray(), ',') : '',
$user->income ? number_format($user->income) : '',
$user->incomeType ? $user->incomeType->jp_type : '',
$user->policy ?: '',
$user->experience ? $user->experience->jp_experience : '',
$user->currentPosition ? $user->currentPosition->jp_required_position : '',
$user->currentContract ? $user->currentContract->jp_required_type : '',
$total,
$technicalTotal,
$otherTotal,
$user->manager_comments ?: ''
]]);
$conf = ["valueInputOption" => "RAW"];
$ins = ["insertDataOption" => "INSERT_ROWS"];
$service->spreadsheets_values->append($spreadsheetId, env('GOOGLE_SHEET_NAME'), $valueRange, $conf, $ins);
}
}
}
}
please help me. Thanks in advance.
