如何使用Google Sheets(v4)API修改取决于特定单元格的特定数据行? [英] How can I use the Google Sheets (v4) API to modify a specific row of data dependent on a specific cell?

问题描述

我想找到一种方法，可以根据我提供的条件使用Google API修改特定的数据行.像SQL一样:

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

我曾尝试使用batchUpdateByDataFilter，但似乎无法使其正常工作.我刚刚意识到Google的查询语言，但这没有更新.

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.

我下面有一个测试电子表格.我希望能够根据ID更新特定的列.

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

例如在SQL中，类似于以下内容

e.g. in SQL, something like the following

UPDATE SET Collected = 'Yes' WHERE ID = 1

推荐答案

• 您要从外部修改电子表格的值.
• 您要使用SQL这样的查询来修改值.
• 您要从外部修改值.

如果我的理解正确，那么这个答案如何?

If my understanding is correct, how about this answer?

不幸的是，在当前阶段，不能使用查询语言更新电子表格的值.您可以在此处看到查询语言的文档.

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.

所以我想提出2个解决方法.

So I would like to propose 2 workarounds.

在这种解决方法中，我想建议使用Sheets API.通过您的最新问题，我可以确认您已经能够使用Sheets API放置和获取值.那么下面的流程呢?

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?

1. 使用sheets.values.get方法从电子表格中检索值.
   • 在共享的电子表格中，从"Sheet1"中检索值.
1. Retrieve values from the Spreadsheet using the method of spreadsheets.values.get.
   • In your shared Spreadsheet, the values are retrieved from "Sheet1".
• 在这种情况下，脚本是您要使用的语言.

解决方法2:

在这种解决方法中，我建议使用由Google Apps脚本创建的Web Apps.电子表格的值是通过使用Web应用程序(如API)进行修改的.

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.

作为示例情况，我使用Web Apps实现以下查询.

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

UPDATE SET Collected = 'Yes' WHERE ID = 1

您共享的电子表格用作示例电子表格.在这种情况下，对于工作表"Sheet1"，当列"A"(ID)的值是1时，列"L"(收集的)的值被修改为Yes.

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.

1. 请打开要使用的电子表格的脚本编辑器.并将以下脚本复制并粘贴到脚本编辑器中.

1. 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");
}

• 请部署Web Apps.

• Please deploy Web Apps.

  1. 在脚本编辑器上，通过发布"->作为Web应用程序部署"打开一个对话框.
  2. 为执行应用程序为:"选择用户正在访问网络应用程序" 或我" .
  3. 为谁有权访问该应用程序:"选择任何人，甚至是匿名的" .这是一个测试用例.
     • 如果使用Only myself，则只有您可以访问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.
  1. 点击查看权限".
  2. 选择自己的帐户.
  3. 在此应用未验证"中单击高级".
  4. 点击转到###项目名称###(不安全)"
  5. 单击允许"按钮.

• 复制当前的Web应用程序URL:".
  • 就像https://script.google.com/macros/s/#####/exec.
  • Copy "Current web app URL:".
    • It's like https://script.google.com/macros/s/#####/exec.

    • 请使用以下curl示例向Web Apps请求.那时，请使用复制的Web应用程序URL.每个值都设置为查询参数.

    • 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=###"
          

        通过curl命令，对于"Sheet1"的工作表，当列"A"(ID)的值是1时，列"L"(收集的)的值被修改为Yes. /p>

        注意:

        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.

        1. 修改Web应用程序的脚本后，请重新部署Web应用程序为新版本.这样，最新脚本将反映到Web Apps.即使修改了脚本也没有重新部署Web Apps时，不会使用最新的脚本.请注意这一点.

        参考文献:

        • 方法:sheets.values.get
        • 方法:sheets.values.update
        • 网络应用
        • 利用Google的Web Apps优势Apps脚本

        References:

        • Method: spreadsheets.values.get
        • Method: spreadsheets.values.update
        • Web Apps
        • Taking advantage of Web Apps with Google Apps Script

        如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

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

        这篇关于如何使用Google Sheets(v4)API修改取决于特定单元格的特定数据行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！