如何在Google Spreadsheets中搜索并查找某一行的坐标 [英] How do I search for and find the coordinates of a row in Google Spreadsheets

问题描述

我有一张Google电子表格，里面有两张纸，一张是数据库包含表单提交，另一种方式是用户可以一次与提交内容交互。

基本上我希望用户能够对提交进行更改并保存它们回到他们来自原始工作表的同一行。

我有将代码发送回去的代码，但我无法弄清楚如何获取坐标：

  function saveChanges（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = ss.getSheets（）[0]; 
 var destination = ss.getSheets（）[1]; 
 
 var range = source.getRange（A40：BL40）; 
 
 //查找单元格A40的值与单元格A中的值匹配的行的坐标：第二个电子表格中的单元格
 
 //在A40：BL40中复制数据源表单到
 // D4：第二个表单中的F6 
 range.copyValuesToRange（目标，1，64，16，16）; 
} 
  

目前数据只写入坐标1，64， 16,16，它只是指向一个当前空行 - 理想情况下，我会将它改为具有正确坐标的变量。

单元格A40的值是唯一的ID和理想的搜索第二张，但我不明白如何。

我很新的Javascript，所以任何帮助将不胜感激。

解决方案

要在表单响应表中查找匹配值，您必须遍历范围才能找到匹配项。有很多方法可以做到这一点，我将展示一对夫妇。

以下是您的 saveChanges（）函数，它将获取目标工作表中的所有数据，通过它的列A查找与 A40 中的值匹配的值，然后更新该行中的数据。

  function saveChanges（）{
 var uniqueIdColIndex = 0; // ColA具有唯一ID，在行数组中是元素0 
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = ss.getSheets（）[0]; 
 var destination = ss.getSheets（）[1]; 
 
 var sourceData = source.getRange（A40：BL40）。getValues（）; 
 var destData = destination.getDataRange（）。getValues（）; 
 
 //查找单元格A40的值与A中的某个单元格匹配的行的坐标
 for（var rowIndex = 0; rowIndex< destData.length; rowIndex ++） {
 if（sourceData [0] [uniqueIdColIndex] == destData [rowIndex] [uniqueIdColIndex]）{
 //找到我们的匹配
 destination.getRange（rowIndex + 1,1，sourceData。 length，sourceData [0] .length）
 .setValues（sourceData）; 
休息; //完成，退出循环
} 
} 
} 
  

这是另一种方法。这一次，我们不读取目标表中的所有数据，只读取列A中的信息。为了能够利用数组查找方法，通过 .getValues（ ）需要先交换 - 所以我们使用一个辅助函数来完成这个任务。 （我使用这个答案中的 transpose（）函数。）

  function saveChanges（）{
 var uniqueIdColIndex = 0; // ColA具有唯一ID，在行数组中是元素0 
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = ss.getSheets（）[0]; 
 var destination = ss.getSheets（）[1]; 
 
 var sourceData = source.getRange（A40：BL40）。getValues（）; 
 //从目标工作表获取列A 
 var destDataTrans = transpose（destination.getRange（1，1，destination.getLastRow（），1）.getValues（））; 
 
 //查找单元格A40的值与A中单元格的匹配行的坐标
 var destRow = destDataTrans [0] .indexOf（sourceData [0]）+ 1; // +1调整为电子表格行
 
 if（destRow> 0）{
 //找到我们的匹配
 destination.getRange（destRow，1，sourceData.length， sourceData [0] .length）
 .setValues（sourceData）; 
 
 
  

第二种方法的代码行数较少，但应该由于转置（）函数会触及列A中的每个元素，然后用 .indexOf（）执行搜索， 。 （第一种方法是搜索到的，一旦找到匹配就退出，所以它实际上做的工作很少。）在这两个例子中，我试图限制尽可能地调用Google的服务。或者，您可以从搜索循环中的电子表格中读取信息，这会比较慢，但会避免保持基于0的 +1 / -1 心理体操数组与基于1的行和列对齐。

I've been searching for quite a while so hopefully no one else has asked this.

I have a Google Spreadsheet with two sheets, one a sort of database containing form submissions and the other a way for users to interact with submissions one at a time.

Basically I want users to be able to make changes to a submission and save them back to the same line that they came from in the original sheet.

I have the code to send the changes back but I can't figure out how to get the coordinates of the correct row:

function saveChanges() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var source = ss.getSheets()[0];
 var destination = ss.getSheets()[1];

 var range = source.getRange("A40:BL40");

  // Find coordinates of the row where value of cell A40 matches a cell in A:A in second spreadsheet

 // This copies the data in A40:BL40 in the source sheet to
 // D4:F6 in the second sheet
 range.copyValuesToRange(destination, 1, 64, 16, 16);
}

At the moment the data is just written to the coordinates "1, 64, 16, 16" which just points to a currently empty row - ideally I'd change that to a variable with the right coordinates.

The value of cell A40 is a unique ID and ideal for searching the second sheet but I can't figure out how.

I'm very new to Javascript so any help would be greatly appreciated.

解决方案

To find your matching value in the form response sheet, you must loop through the range to find a match. There are a number of ways to do that, I'll show a couple.

Here's a version of your saveChanges() function that will get all the data from your destination sheet, look through it's column A for a match to the value in A40, then update the data in that row.

function saveChanges() {
  var uniqueIdColIndex = 0;  // Col "A" has unique ID, is element 0 in row array

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

  var sourceData = source.getRange("A40:BL40").getValues();
  var destData = destination.getDataRange().getValues();

  // Find coordinates of the row where value of cell A40 matches a cell in A:A in second spreadsheet
  for (var rowIndex=0; rowIndex < destData.length; rowIndex++) {
    if (sourceData[0][uniqueIdColIndex] == destData[rowIndex][uniqueIdColIndex]) {
      // Found our match
      destination.getRange(rowIndex+1,1,sourceData.length,sourceData[0].length)
                 .setValues(sourceData);
      break; // Done, exit loop
    }
  }
}

Here's another way to do it. This time, we don't read all the data in the destination sheet, only the info in column A. To be able to take advantage of array lookup methods, the two-dimensional array retrieved via .getValues() needs to be transposed first - so we use a helper function to do that. (I'm using the transpose() function from this answer.)

function saveChanges() {
  var uniqueIdColIndex = 0;  // Col "A" has unique ID, is element 0 in row array

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

  var sourceData = source.getRange("A40:BL40").getValues();
  // Get column A from destination sheet
  var destDataTrans = transpose(destination.getRange(1, 1, destination.getLastRow(),1).getValues());

  // Find coordinates of the row where value of cell A40 matches a cell in A:A in second spreadsheet
  var destRow = destDataTrans[0].indexOf(sourceData[0]) + 1;  // +1 to adjust to spreadsheet rows

  if (destRow > 0) {
    // Found our match
    destination.getRange(destRow,1,sourceData.length,sourceData[0].length)
               .setValues(sourceData);
  }
}

The second approach has fewer lines of code, but should be a bit slower than the first one because of the transpose() function which touches every element in column A before performing a search with .indexOf(). (The first approach searched in place, and exited once a match was found, so it actually does less work.)

In both examples, I've tried to limit the calls to google's services as much as possible. Alternatively, you could read info from the spreadsheets inside the search loop, which would be much slower, but would avoid the +1 / -1 mental gymnastics needed to keep 0-based arrays aligned with 1-based rows and columns.

这篇关于如何在Google Spreadsheets中搜索并查找某一行的坐标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！