How can I use the Google Sheets (v4) API to modify a specific row of data dependent on a specific cell?

Question

I want to find a way of using the Google API to modify a specific row of data based on criteria I supply. Something like a SQL's :

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1

I have tried playing around with batchUpdateByDataFilter but cannot seems to get it to work. I just became aware of Googles Query language but this does not have an update.

I have a test spreadsheet below. I want to be able to update specific columns based on the ID.

https://docs.google.com/spreadsheets/d/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8/edit?usp=sharing

e.g. in SQL, something like the following

UPDATE SET Collected = 'Yes' WHERE ID = 1

There is no sql like method to update the entire data. If id = row number, it shouldn't be hard to create a1range: required for datafilter. — TheMaster

Tanaike Tanaike · Accepted Answer · 2019-09-01T01:31:57

You want to modify the values of Spreadsheet from outside.
You want to modify the values with the query like SQL.
You want to modify the values from outside.

If my understanding is correct, how about this answer?

Issue:

Unfortunately, in the current stage, the values of Spreadsheet cannot updated using the query language. You can see the document of the query language at here. This has already been mentioned by TheMaster's comment.

So I would like to propose 2 workarounds.

Workaround 1:

In this workaround, I would like to propose to use Sheets API. From your latest question, I could confirm that you have already been able to put and get values using Sheets API. So how about the following flow?

Retrieve values from the Spreadsheet using the method of spreadsheets.values.get.
- In your shared Spreadsheet, the values are retrieved from "Sheet1".
The retrieved values are modified using a script.
- In this case, the script is the language you want to use.
Put the modified values to the sheet of "Sheet1" using the method of spreadsheets.values.update.

Workaround 2:

In this workaround, I would like to propose to use Web Apps which was created by Google Apps Script. The values of Spreadsheet are modified by using the Web Apps like an API.

As a sample situation, I achieve the following query using Web Apps.

UPDATE SET Collected = 'Yes' WHERE ID = 1

Your shared Spreadsheet is used as the sample Spreadsheet. In this case, for the sheet of "Sheet1", when the value of column "A" (ID) is 1, the value of column "L" (Collected) is modified to Yes.

Usage:

Please open the script editor of the Spreadsheet you want to use. And copy and paste the following script to the script editor.

function doGet(e) {
  var p = e.parameter;
  var sheetName = "Sheet1";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();
  var headers = values.splice(0, 1)[0];
  var checkCol = headers.indexOf(p.checkKey);
  var modifyCol = headers.indexOf(p.modifyKey);
  values.forEach(function(e, i) {
    if (e[checkCol] == p.checkValue) {
      values[i][modifyCol] = p.modifyValue;
    }
  });
  values.unshift(headers);
  range.setValues(values);
  return ContentService.createTextOutput("Done");
}

Please deploy Web Apps.
1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "User accessing the web app" or "Me" for "Execute the app as:".
3. Select "Anyone, even anonymous" for "Who has access to the app:". This is a test case.
  - If Only myself is used, only you can access to Web Apps. At that time, please use your access token.
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
  1. Click "Review Permissions".
  2. Select own account.
  3. Click "Advanced" at "This app isn't verified".
  4. Click "Go to ### project name ###(unsafe)"
  5. Click "Allow" button.
6. Copy "Current web app URL:".
  - It's like https://script.google.com/macros/s/#####/exec.
7. Click "OK".

Please request to the Web Apps using the following curl sample. At that time, please use the copied URL of Web Apps. The each value is set as the query parameter.

curl -L "https://script.google.com/macros/s/###/exec?checkKey=ID&checkValue=1&modifyKey=Collected&modifyValue=Yes"

Or, if you want to use your access token, please use below.

curl -L "https://script.google.com/macros/s/###/exec?checkKey=ID&checkValue=1&modifyKey=Collected&modifyValue=Yes&access_token=###"

By above curl command, for the sheet of "Sheet1", when the value of column "A" (ID) is 1, the value of column "L" (Collected) is modified to Yes.

Note:

When you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. When the Web Apps is not redeployed even when the script is modified, the latest script is not used. Please be careful this.

References:

If I misunderstood your question and this wat not the direction you want, I apologize.