Google API: Loop through mySQL results to add multiple rows to Google Sheet

Question

I dreaded posting this because I should have been able to figure this out. All I am trying to do is loop through the results in a mySQL query and add those rows into Google Sheets. I do not want to add them one at a time, but I cannot figure out how to do this, adding multiple rows at once. I want to do this because I want to add a color row if a PONumber changes.

  $service = new \Google_Service_Sheets($client);
 $spreadsheetId = 'xxxxxxxxxxx';
 $spreadsheet = $service->spreadsheets->get($spreadsheetId);
    $result = $db->query("SELECT * from pomasterview"); 
    $oldref='1';
    if($result->num_rows > 0){ $i=0; 
        while($row = $result->fetch_assoc()){ $i++; 
          if ($oldref != $row['refnumber']){
        //if ponumber changes add a new row 
            }
    $refnumber=$row['refnumber'];
    $partitem=$row['partitem'];
    $partqty=$row['partqty'];
    $oldref=$refnumber;

$newRow = [$partitem,$partqty];
$rows = [$newRow]; 
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1'; 
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);

}}

The above code adds one row at a time. I want it to add all the rows at the same time.

Thank you.

Tanaike · Accepted Answer · 2024-07-23 00:56:43Z

About your following expected result,

The above code adds one row at a time. I want it to add all the rows at the same time.

In your script, how about the following modification?

Modified script:

$service = new \Google_Service_Sheets($client);
$spreadsheetId = 'xxxxxxxxxxx';
$spreadsheet = $service -> spreadsheets -> get($spreadsheetId);
$result = $db -> query("SELECT * from pomasterview");
$oldref = '1';
if ($result -> num_rows > 0) {
  $rows = array();
  $i = 0;
  while ($row = $result -> fetch_assoc()) {
    $i++;
    if ($oldref != $row['refnumber']) {
      //if ponumber changes add a new row 
    }
    $refnumber = $row['refnumber'];
    $partitem = $row['partitem'];
    $partqty = $row['partqty'];
    $oldref = $refnumber;

    $newRow = [$partitem, $partqty];
    array_push($rows, $newRow);
  }
  $valueRange = new \Google_Service_Sheets_ValueRange();
  $valueRange -> setValues($rows);
  $range = 'Sheet1';
  $options = ['valueInputOption' => 'USER_ENTERED'];
  $service -> spreadsheets_values -> append($spreadsheetId, $range, $valueRange, $options);
}

In this modification, $newRow is put into $rows in the loop. And, the values $rows are put into the Spreadsheet using append method.

Collectives™ on Stack Overflow

Google API: Loop through mySQL results to add multiple rows to Google Sheet

1 Answer 1

Modified script:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Comments

Your Answer

Sign up or log in

Post as a guest

Related