I have php code to send form data to Google Sheets and a tracking sheet. Is there a way to read the date in the rows already on the Google Sheet and insert the data in the correct location. For example, if the sheet has 5/1/24 and 5/15/24 and the code is sending a new row with the date 5/10/24, I would like the row to go between the previous 2 rows instead of below the 5/15/24 row.
public function submitInvoice(Request $request)
{
$arr = $request->all();
$request->validate([
"officename" => "required",
"workdate" => "required",
"Completed" => "required",
"self" => "required",
]);
$provider_traker = array(
'person_id' => $request->person_id,
'officename' => $request->officename,
'workdate' => $request->workdate,
'Completed' => $request->Completed,
'self' => $request->self,
'first' => $request->first,
'last' => $request->last,
);
PersonTracker::insert($person_traker);
// Session::forget('provider');
$sheetObj = $this->sheet($request->officename);
if ($sheetObj) {
$sheetData = $sheetObj->get();
$workdate = $request->workdate;
$dateObj = DateTime::createFromFormat('m-d-Y', $workdate);
$monthName = $dateObj->format('F');
$start_checking = false;
$row = "";
foreach ($sheetData as $key => $data) {
if($start_checking) {
if(isset($data[0]) && $data[0] == "") {
$row = $key;
$row++;
break;
}
}
if(isset($data[0]) && $data[0] == $monthName) {
$start_checking = true;
}
}
$person = Person::find($request->person_id);
$person_name = $person->firstname.' '.$person->lastname;
$sheetObj->range('A'.$row)->update([[
$request->workdate,
"",
"",
"",
$request->Completed,
"",
"",
$request->first,
"",
$request->last,
"",
$request->self,
"",
"",
$person_name,
]]);
}