如何找到有价值的单元格位置 [英] How find location of cell with some value

问题描述

我需要在电子表格中找到包含一些数据的行并将其删除. 我所知道的是该行中一个单元格内的ID.

I need to find a row in my spreadsheet that contains some data and delete it. all I know is the id inside a cell on the row.

如何使用Google REST API搜索单元格的位置

How can I perform a search for the location of the cell with google REST API

例如:

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:find?value=123456

response:
 sheet1! B2:B2

也: 我也想支持从一张纸到另一张纸的剪切和粘贴. 因此将元数据放在该行上将不起作用，因为在将单元格从该行复制到另一行时会丢失. (没有REST API可以将尺寸(行|列)从一张纸剪切并粘贴到另一张纸.

Also: I also want to support cut&paste from one sheet to another. so put metadata on the row can't work because it lost when copying cells from the row to another row. ( there is no REST API to cut&paste dimension (row||column) from one sheet to another.

如果有一种方法可以在我简单的工作表之间复制和粘贴尺寸

If there is a way to copy&paste dimension between sheet I simple can use

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter

{
  "dataFilters": [
    {
      object (DataFilter)
    }
  ],
  "majorDimension": enum (Dimension),
  "valueRenderOption": enum (ValueRenderOption),
  "dateTimeRenderOption": enum (DateTimeRenderOption)
}

查找并删除该行.

推荐答案

• 您想通过使用Sheets API搜索值来检索行号.
• 您要通过使用Sheets API搜索值来删除行.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

不幸的是，在当前阶段，无法通过使用Sheets API搜索值来直接检索行号.而且，不能通过使用Sheets API搜索值来删除行.我不确定是否在将来的更新中添加了这些.因此，在当前解决方法中，以下解决方法如何?

Unfortunately, in the current stage, the row number cannot be directly retrieved by searching a value with Sheets API. And also, the rows cannot be deleted by searching a value using Sheets API. I'm not sure whether these are added at the future update. So in the current workaround, how about the following workarounds?

此解决方法的流程如下.

The flow of this workaround is as follows.

1. 使用Sheets API中的"spreadsheets.get"方法检索所有工作表名称和工作表ID.
2. 使用"spreadsheets.values.batchGet"表格API的方法从电子表格中的表格中检索所有值.
3. 使用值从检索到的值中搜索工作表名称和行号.
   • 在这种情况下，需要准备脚本.
1. Retrieve all sheet names and sheet ID using the method of "spreadsheets.get" in Sheets API.
2. Retrieve all values from the sheet in the Spreadsheet using the method of "spreadsheets.values.batchGet" Sheets API.
3. Search the sheet name and row number from the retrieved values using a value.
   • In this case, it is required to prepare a script.

通过这种流程，您的目标可以实现.

By this flow, your goal can be achieved.

在此替代方法中，将由Google Apps脚本创建的Web Apps用作API.

In this workaround, Web Apps created by Google Apps Script is used as an API.

Web Apps的示例脚本是Google Apps脚本.因此，请创建一个Google Apps脚本项目.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

如果您想直接创建它，请访问 https://script.new/.在这种情况下，如果您未登录Google，则会打开登录"屏幕.因此，请登录到Google.这样，将打开Goog​​le Apps脚本的脚本编辑器.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

请复制以下脚本并将其粘贴到脚本编辑器中.该脚本适用于Web Apps.在此脚本中，使用了Sheets API.因此，请在高级Google服务中启用Sheets API.

Please copy and paste the following script to the script editor. This script is for the Web Apps. In this script, Sheets API is used. So please enable Sheets API at Advanced Google services.

function doGet(e) {
  var findText = e.parameter.value;
  var spreadsheetId = e.parameter.spreadsheetId;
  var deleteRows = e.parameter.deleteRows && e.parameter.deleteRows.toLowerCase() === "true";
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var ranges = ss.createTextFinder(findText).findAll();
  if (deleteRows === true) {
    var requests = ranges.reverse().map(r => ({deleteDimension:{range:{dimension:"ROWS",sheetId:r.getSheet().getSheetId(),startIndex:r.getRow() - 1,endIndex:r.getRow()}}}));
    Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
  }
  var res = ranges.map(r => ({range: `'${r.getSheet().getSheetName()}'!${r.getA1Notation()}`, sheetId: r.getSheet().getSheetId()}));
  return ContentService.createTextOutput(JSON.stringify(res));
}

• 在这种情况下，将使用GET方法.当您想通过提供大数据来运行该函数时，可以使用doPost()代替doGet().
• In this case, the GET method is used. When you want to run the function by giving the large data, you can use doPost() instead of doGet().
1. 在脚本编辑器上，通过发布"->作为Web应用程序部署"打开一个对话框.
2. 为以以下方式执行应用":选择我" .
   • 通过这种方式，脚本以所有者身份运行.
   • 在这里，当设置为"Anyone"时，脚本将以每个用户的身份运行.在这种情况下，需要将脚本共享给每个用户.并且要求使用访问令牌.请注意这一点.
1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
   • Here, when "Anyone" is set, the script is run as each user. In this case, it is required to share the script to each user. And the access token is required to be used. Please be careful this.
• 在这种情况下，不需要请求访问令牌.我认为，作为测试用例，我建议使用此设置.
• 当然，您也可以使用访问令牌.那时，请将其设置为任何人" .
1. 点击查看权限".
2. 选择自己的帐户.
3. 在此应用未验证"中单击高级".
4. 点击转到###项目名称###(不安全)"
5. 单击允许"按钮.
• 单击确定".
• 复制Web应用程序的URL.就像https://script.google.com/macros/s/###/exec.
  • 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.
  • Click "OK".
  • Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

    3.使用Web Apps运行该功能.

    这是使用Web Apps的示例curl命令.请设置您的Web Apps URL，sheetsheetId和搜索值.

    3. Run the function using Web Apps.

    This is a sample curl command for using Web Apps. Please set your Web Apps URL, spreadsheetId and the search value.

    curl -GL \
      -d "spreadsheetId=###" \
      -d "value=sample" \
      "https://script.google.com/macros/s/###/exec"
    

    • 当spreadsheetId=###和value=sample用作doGet(e)处的查询参数时.例如，您可以使用e.parameter.spreadsheetId检索spreadsheetId.

    • 在Web Apps的上述脚本中，运行此curl命令时，将返回诸如[{"range":"'Sheet1'!A1","sheetId":0},{"range":"'Sheet2'!B2","sheetId":###}]的值.这是通过搜索值sample得出的.

      • When spreadsheetId=### and value=sample are used as the query parameter at doGet(e). For example, you can retrieve spreadsheetId using e.parameter.spreadsheetId.

      • At above script of Web Apps, when this curl command is run, the value like [{"range":"'Sheet1'!A1","sheetId":0},{"range":"'Sheet2'!B2","sheetId":###}] is returned. This is the result by searching a value of sample.

        • 作为示例案例，从电子表格中的所有表格中搜索值.为此，您可以根据实际情况修改脚本.

        如果按如下方式使用deleteRows=true，则从电子表格中的所有工作表中搜索值，并删除搜索到的行.

        If deleteRows=true is used like below, the value is searched from all sheets in the Spreadsheet and the searched rows are deleted.

        curl -GL \
          -d "spreadsheetId=###" \
          -d "value=sample" \
          -d "deleteRows=true" \
          "https://script.google.com/macros/s/###/exec"
        

      • 此示例脚本与V8一起使用.
      • 方法:sheetssheets.get
      • 方法:sheetssheets.values.batchGet
      • 方法:sheetssheets.batchUpdate
      • 网络应用
      • 利用Google的Web Apps优势Apps脚本
      • 高级Google服务
      • Method: spreadsheets.get
      • Method: spreadsheets.values.batchGet
      • Method: spreadsheets.batchUpdate
      • Web Apps
      • Taking advantage of Web Apps with Google Apps Script
      • Advanced Google services

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

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

      这篇关于如何找到有价值的单元格位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！