phpgoogle-sheetsgoogle-sheets-api

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

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.

Solution

  • 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);
}