根据Google表格中的URL查找并选择一个单元格 [英] find and select a cell based on URL in google sheets

问题描述

我使用Google表格创建了一个简单的基于表格的化学品数据库. 工作表如下所示: https://docs.google.com/spreadsheets/d/13sLioJr_T6lqQ7y_pStBR8CKxReYwLUn4hao/edit#gid = 292509613& range = B2

该链接将转换为QR码，供需要从电话远程更改电池值的人使用，而无需手动搜索特定的化学物质

问题开始于有人介绍新行，然后更改了每个单元格的位置，现在所有的网址(即打印的QR码)选择了错误的化学品.现在所有的网址都是错误的，我必须手动解决此问题并将新行移到表格底部

理想情况下，我想根据化学药品的ID(在本例中为cp1000)选择要查询的单元格 类似于(用伪代码) docs.google.com/spreadsheets/d/13sLioJr ..../edit#gid = 292509613& range =(选择单元格A包含(cp1000)的单元格B(row_number))

成功结果的一个例子是根据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的方法? 提前谢谢

解决方案

答案:

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

详细信息:

您可以将每个QR码设置为包含化学药品ID作为URL参数，然后根据参数是什么来加载工作表:

网络应用程序网址示例:

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

应用脚本代码:

 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所在的对应行，最后使用该单元格的范围重定向到工作表您需要.

I made a simple sheet based database for chemicals using google sheets. The sheet looks like this: https://docs.google.com/spreadsheets/d/e/2PACX-1vR0-AMEKNM3ZbDq67OIKWnc7E3KP8kfOsnr0Bjg2OSjpevLLjniknGXfIiiyZvbwE9bz3EfbOpO46ef/pubhtml?gid=292509613&single=true

There are many rows and columns. A user can change a value of a cell using a url link something like this https://docs.google.com/spreadsheets/d/13sLioJr_T6lqQ7y_pStBR8CKxReYwLUn4hao/edit#gid=292509613&range=B2

the link is converted to a QR code, which is used by someone who need to remotely change the value of the cell from phone, without searching for a specific chemical manually

The problems start when someone introduces a new row, and then location of each cell is changed, now all the url (i.e printed QR codes) select wrong chemicals. Now all the urls are wrong, and I have to manually fix this and move the new row to the bottom of the table

Ideally I would want select a cell with a query based on the chemical's ID (in this case cp1000) something like (in pseudocode) docs.google.com/spreadsheets/d/13sLioJr..../edit#gid=292509613&range=(select cell B(row_number) where cell A contains(cp1000))

An example of sucessful outcome will be selection of cell in column B based on column A

in pseudocode:

 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

is there anyway to write it in a URL? Big thanks in advance

解决方案

Answer:

You can build a web-app which redirects to the cell you need based on the outcome of a conditional statement.

Details:

You can set each of the QR codes to contain the chemical ID as a URL parameter and then load the sheet based on what the parameter is:

Example Web-App URL:

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

Apps Script code:

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);
    }
  }
}

This will take the parameter, find which row it is in and return the cell BX where X is the corresponding row to which the chemNo is in, and finally redirect to the sheet with the range of the cell that you need.

这篇关于根据Google表格中的URL查找并选择一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！