在Google表格中基于URL查找并选择单元格

Question

我使用 Google Sheets 制作了一个基于化学品的简单表格数据库。 这个表格看起来像这样： https://docs.google.com/spreadsheets/d/e/2PACX-1vR0-AMEKNM3ZbDq67OIKWnc7E3KP8kfOsnr0Bjg2OSjpevLLjniknGXfIiiyZvbwE9bz3EfbOpO46ef/pubhtml?gid=292509613&single=true 有很多行和列。用户可以使用 URL 链接更改单元格的值, 类似这样的链接： https://docs.google.com/spreadsheets/d/13sLioJr_T6lqQ7y_pStBR8CKxReYwLUn4hao/edit#gid=292509613&range=B2 该链接被转换为 QR 码，由需要远程从手机更改单元格的人使用，无需手动搜索特定的化学品。

当有人引入新行时，问题就开始了, 每个单元格的位置都改变了，现在所有的 URL (例如打印的 QR 码) 都选择了错误的化学品。现在所有的 URL 都是错误的，我必须手动修复这个问题并将新行移至表格底部。

理想情况下，我想要根据化学物质的 ID (在这种情况下为 cp1000) 使用查询选择一个单元格，类似于 (伪代码中的) docs.google.com/spreadsheets/d/13sLioJr..../edit#gid=292509613&range=(select cell B(row_number) where cell A contains(cp1000))

成功的例子将会基于列A选择列B中的单元格。

伪代码如下：

 cell = Cell()
 id = query.exctract_id()
 for n in range(1,max_row):
    if cell(column_number = 'A', row_number = n).value == id:
        select cell(column_number ='B', row_number = n)
        break

有没有任何方法可以在URL中编写它？

- Sergey Tsarev

---

您能提供您期望的结果吗？当然可以，请删除您的个人信息。顺便说一下，在您的问题中，有三种类型的终端节点，分别是docs.google.com/spreadsheets/d/e/###/pubhtml、docs.google.com/spreadsheets/d/###/edit和docs.google.com/spreadsheets/d/###/editedit。我能问您一下关于它们的问题吗？ - Tanaike

请问您能否分享一份电子表格的副本，并包含一个成功结果的伪代码示例。 - Tedinoz

使用伪代码示例纠正了我的文本。URL中的“editedit”是一个打字错误。 - Sergey Tsarev

Answer 1

答案：

您可以构建一个 Web 应用程序，根据条件语句的结果重定向到所需的单元格。

详情：

您可以将每个 QR 码设置为包含化学 ID 作为 URL 参数，然后根据参数加载表格：

示例 Web 应用程序 URL：

https://script.google.com/a/google.com/macros/s/<web-app-ID>/exec?chemNo=cp1000

Apps Script 代码：

function doGet(e){
  var sheetUrl = "<your-spreadsheet-URL-with-gid>";
  var cell = getCell(e.parameter.chemNo, url);

  return HtmlService.createHtmlOutput('<script>window.location.replace("' + sheetUrl + '&range=' + cell + '"</script>');
}

function getCell(chemNo, url){
  
  //get the first sheet of your 
  var sheet = SpreadsheetApp.openByUrl(url).getSheets()[0];
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('A1:A' + lastRow).getValues();
  
  for(var i = 0; i < lastRow; i++){
    if(range[i] == chemNo){
      return 'B' + (i + 1);
    }
  }
}

这将接受参数，找到它所在的行，并返回单元格BX，其中X是对应于chemNo所在的行，最后重定向到包含所需单元格范围的工作表。

- Rafa Guillermo

Answer 2

感谢您的建议。 我最终制作了一个简单的1页Web应用程序。学习JS实际上非常容易。 我使用了一种有点奇怪的方法来从doGet()函数中传递变量，即将它们存储在电子表格中，因为该函数只能返回HTML输出。

function doGet(e){
  var url = "https://docs.google.com/spreadsheets/d/13sLioJr_T6lRODrvGSqQ7y_pStBR8CKxReYwLUn4hao/edit#gid=0";
  var [ID, chemical, totalAmount, name, targetRow, unit] = fill(e);
  var ss = SpreadsheetApp.openByUrl(url).getSheetByName("Supplementary");
  ss.getRange("B5").setValue(ID);
  ss.getRange("B6").setValue(name);
  ss.getRange("B7").setValue(targetRow);
  ss.getRange("B8").setValue(Session.getActiveUser().getEmail());
  Logger.log(ID);
  var data = {ID:ID, chemical :chemical, totalAmount :totalAmount, unit :unit};
   var html = HtmlService
      .createTemplateFromFile('FrontEnd.html')
      .evaluate()
      .getContent();
   return HtmlService
     .createTemplate(html +    
       "<script>\n" + 
        "postChemicals( " +
        JSON.stringify(data) +
        ");\n</script>")
        .evaluate();
}
//  


function fill(e){
  // this function extracts event parameters and gets a cell from spreadsheet with query from URL
  // where name equals to spreadsheet name and ID to the chemical ID generated by "Code.gs"
  var url = "<post your url>";
    var ID = e.parameter.ID;
    var name = e.parameter.name;
  var targetRow =getCell(ID,name);
  var sheet = SpreadsheetApp.openByUrl(url).getSheetByName(name);
  var chemical = sheet.getRange("B"+targetRow).getValue()
  var totalAmount = sheet.getRange("D"+targetRow).getValue()- 
  sheet.getRange("C"+targetRow).getValue();
  var takenCell = sheet.getRange("C"+targetRow);
  var unit = sheet.getRange("E"+targetRow).getValue();
  Logger.log("fill(e) called");
  return [ID, chemical, totalAmount,name, targetRow, unit ]
}

function getCell(chemNo, name){
  var url = "https://docs.google.com/spreadsheets/d/13sLioJr_T6lRODrvGSqQ7y_pStBR8CKxReYwLUn4hao/edit#gid=0";
 // this is a function that searches for a cell
  //get the first sheet of your 
  var sheet = SpreadsheetApp.openByUrl(url).getSheetByName(name);
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('A1:A' + lastRow).getValues();

  for(var i = 0; i < lastRow; i++){
    if(range[i] == chemNo){
      return  (i + 1);
    }
  }

- Sergey Tsarev