3
$valueRange = new Google_Service_Sheets_ValueRange();

// You need to specify the values you insert
$valueRange->setValues(["values" => ["$now", "SKIP", "SKIP", "SKIP", "$quantity", "SKIP", "SKIP"]]); // Add two values

// Then you need to add some configuration
$conf = ["valueInputOption" => "USER_ENTERED"];

// Update the spreadsheet
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);

I only want to update specific columns in the row I am editing, how do I skip the ones that I don't want it to touch?

Where I have put "SKIP" I want it to completely ignore that column and keep whatever information is already in it

Miss Chanandler Bong
  • 4,081
  • 10
  • 26
  • 36
Jk135
  • 31
  • 2
  • Why not write some code to achieve that? – Nico Haase Sep 12 '19 at 13:57
  • What code would I write? – Jk135 Sep 12 '19 at 14:00
  • The code to solve your problem :) If you don't want to set specific values, what happens if you just don't set them? – Nico Haase Sep 12 '19 at 14:15
  • It rather depends on what values (if any) were already in those cells. If there were existing values, then it would make sense to insert them into the `setValues()` statement. If there weren't existing values, then you could insert a value of '""', perhaps. – Tedinoz Sep 13 '19 at 03:03
  • I have this question too! I want to update a row and leave some cells untouched. Using `""` causes the cell to be cleared. Using `null` throws a Google_Service_Exception and results in a 500 error. The documentation says leaving it "empty" will ignore the cell and leaving an "empty string" will clear the cell. But I have no idea how to leave it empty and get the desired result. Worse, leaving two commas in a row results in a PHP error. – smts Mar 11 '21 at 05:28
  • 2
    @smts Found this [bug report](https://github.com/googleapis/google-api-php-client/issues/979) which suggests using `Google_Model::NULL_VALUE` for this. Does it solve the issue? In case it does, then this question is a duplicate of [this one](https://stackoverflow.com/questions/45025955/write-selectively-to-a-range-in-spreadsheet-not-working-with-null-values-in-php). – El_Vanja Mar 11 '21 at 14:21
  • @El_Vanja -- I'll check tonight – smts Mar 11 '21 at 20:56
  • @smts Although I'm not sure whether I could correctly understand about your goal, at [the method of spreadsheets.values.append in Sheets API](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append), the values are appended to the next row of the last row in the data range. By this, when you have a sheet including 5 rows and 5 columns of "Sheet1" and you want to put the values to the row 5, even when you use `Sheet1!A5` as the range, the value is put to the row 6. It seems that this is the current specification. – Tanaike Mar 12 '21 at 05:13
  • @smts So, in your situation, how about using [the method of spreadsheets.values.update](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update)? In this case, you can use `null` as the skip cell and you can use `Sheet1!A5` to above sample situation for putting value. If I misunderstood your goal, I apologize. – Tanaike Mar 12 '21 at 05:13

1 Answers1

1

According to this issue using Google_Model::NULL_VALUE where the column value should be skipped solves the problem.

Lyuboslav Alexandrov
  • 347
  • 1
  • 2
  • 10