Google应用程序脚本 - 使用for循环根据条件从范围中提取数据 [英] Google apps script - using for loop to pull data from range based on condition

问题描述

我有一个问题，在Google工作表中的for循环中拉出正确的值。
这里是我的代码：
注意：这是一个更大函数的代码片段

  function sendEmails（）{
 var trackOriginSheet = SpreadsheetApp.getActiveSpreadsheet（）。getName（）; 
 var getMirSheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Miranda）; 
 
 //设置一个新的对象来保存条件数据
 var holdingData = new Object（）; 
 
 //创建函数从原始表单获取值
 var returnedValues = function（trackOriginSheet）{
 
 //通过returnedValues（） 
 if（trackOriginSheet === getMirSheet）{
 
 var startMirRow = 2; //处理
的第一行数据var numRowsMir = 506; //要处理的行数
 
 //获取单元格范围A2：Z506 
 var dataRangeMir = getMirSheet.getRange（startMirRow，1，numRowsMir，26）; 
 
 //获取Range中每个单元格的值。 
 var dataMir = dataRangeMir.getValues（）; 
 
（dataMir中的var k）{
 var secondRowMir = dataMir [k]; 
 var intRefDescMir = secondRowMir [3]; 
 var intAdminActionsMir = secondRowMir [4]; 
 
 //将返回的数据推送到holdingData对象
 holdingData.selectedData = secondRowMir; 
 holdingData.refDesc = intRefDescMir; 
 holdingData.adminActions = intAdminActionsMir; 
 
 
 $ b code 
 $ b 
 以下是我正在处理的工作表副本 
 
 
 
我首先需要在这里发生的事情是跟踪原始表单，然后创建一个对象来保存从returnedValues（）函数返回的数据。后来，我将这个对象属性调用到一个发送邮件函数中。
 
 
问题是我需要能够从选定的工作表中动态地提取数据（换句话说，当用户选择Miranda表格I栏中的是选项时，这个脚本需要做的第一件事就是把变量的值拉到for循环在用户选择是的同一行内。然后，我将这些数据推送到一个自定义对象以后再调用。
 
 
 <对我来说很明显，我做错了。至少，我的循环出了问题。我做了什么？ ：）
 
 
 编辑： 
在阅读VyTautas的建议之后，我尝试了一个工作循环：
 （var k = 0; k  var mirColI = dataMir [k] [8] b 
 $ b 
  ; 
 var mirRefDesc = dataMir [k] [2]; 
 var mirAdminActions = dataMir [k] [3]; 
 var mirDates = dataMir [k] [4]; 
 if（mirColI ===Yes）{
 var activeRowMir = mirColI.getActiveSelection.getRowIndex（）; 
 //当选择Yes时，从活动行中拉取所选值
 var mirRefDescRange = getMirSheet.getRange（activeRowMir，mirRefDesc）; 
 var mirRefDescValues = mirRefDescRange.getValues（）; 
 var mirAdminActionsRange = getMirSheet.getRange（activeRowMir，mirAdminActions）; 
 var mirAdminActionsValues = mirAdminActionsRange.getValues（）; 
 var mirDatesRange = getMirSheet.getRange（activeRowMir，mirDates）; 
 var mirDatesValues = mirAdminActionsRange.getValues（）; 
 var mirHoldingArray = [mirRefDescValues，mirAdminActionsValues，mirDatesValues]; 
 //将mirHoldingArray的值push到holdData 
 holdingData.refDesc = mirHoldingArray [0]; 
 holdingData.adminActions = mirHoldingArray [1]; 
 holdingData.dates = mirHoldingArray [2]; 
 
 
 $ / code $ 
 
 
解决方案
在实际的脚本编辑器中做了所有的空白吗？ ：D 
 
 
 
您已经正确使用 .getValues（）将整个表格拉到一个数组中。你现在需要做的是有一个for循环经过 dataMir [k] [8] ，并简单地获取数据 if dataMir [k] [ 8] ==='是'。 （var k = 0; k< dataMir.length）中的（var k in dataMir）作为并不是必须的。 ; k ++）是一个更清洁，你有一个for循环，保证控制（虽然这可能是更偏好的东西）。也可以减少你使用的变量的数量
 
 $ $ $ $ $ $ $ $ holdData.selectedData = mirData [k] 
 holdingData.refDesc = mirData [k] [2] //我假设你想为这个变量的第三列，而不是第四个
 holdingData.adminActions = mirData [k] [3] //同上
  

请记住，数组以0开始，所以如果您 mirData [k] [0] 是列A， mirData [k] [1] 是B列等。

编辑：您在编辑中所写的内容似乎是在代码上加倍的。你已经有了这些数据，但是你正在尝试再次拖动它，并且你使用的一些变量会给你一个错误。我会从if中删除代码，尽管我不明白为什么你需要同时获得活动的表单和表单。如果你知道这个名字是不变的，那么只要通过名字（或者索引）就可以得到正确的表格，这样就可以避免使用错误的表格。

  var titleMirRows = 1; //处理
的第一行数据var numRowsMir = getMirSheet.getLastRow（）; //要处理的行数
 
 //获取单元格范围A2：Z506 
 var dataRangeMir = getMirSheet.getRange（titleMirRows + 1，1，numRowsMir  -  titleMirRows，26）; //可能需要调整，但是现在只能得到与数据一样多的行，对于列也可以做同样的操作
 
 //获取Range中每个单元格的值。 
 var dataMir = dataRangeMir.getValues（）; （var k = 0; k  if（dataMir [k] [7] ==='Yes'）{//假设你的意思是列
 holdingData.refDesc = dataMir [k] //这将存储整行
 holdingData.adminActions = dataMir [k] [3] //这个存储列D 
 holdingData .dates = dataMir [k] [4] //存储列E 
} 
} 
  

仔细检查我添加到这些变量的列是否是你想要的。据我所知，对象存储整个行数组，名为 Administrative Actions 的列中的值以及列日期/期间（如果适用）中的值。如果没有，请相应调整，但正如您所看到的，我们通过简单地操作整个数据数组，最大限度地减少了我们对工作表本身所做的工作。尽可能少拨打Google服务电话。

I'm having an issue pulling the correct values out of a for loop in Google sheets. Here's my code: Note: this is a snippet from a larger function

function sendEmails() {
var trackOriginSheet = SpreadsheetApp.getActiveSpreadsheet().getName();
var getMirSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Miranda");

//Set a new object to hold conditional data
var holdingData = new Object();

//Create function to get values from origin sheet
var returnedValues = function (trackOriginSheet) {

//Load dynamic variables into an object via returnedValues()
    if (trackOriginSheet === getMirSheet) {

        var startMirRow = 2; // First row of data to process
        var numRowsMir = 506; // Number of rows to process

        // Fetch the range of cells A2:Z506
        var dataRangeMir = getMirSheet.getRange(startMirRow, 1, numRowsMir, 26);

        // Fetch values for each cell in the Range.
        var dataMir = dataRangeMir.getValues();

        for (var k in dataMir) {
            var secondRowMir = dataMir[k];
            var intRefDescMir = secondRowMir[3];
            var intAdminActionsMir = secondRowMir[4];

            //Push returned data to holdingData Object
            holdingData.selectedData = secondRowMir;
            holdingData.refDesc = intRefDescMir;
            holdingData.adminActions = intAdminActionsMir;
        }
    }
}

Here's a copy of the sheet I'm working on

What I need to have happen here first, is track the origin sheet, then create an object to hold data returned from the returnedValues() function. Later, I'll call this objects properties into a send email function.

The problem is that I need to be able to pull data from the selected sheet dynamically (the "Miranda" sheet in this case.) In other words, when a user selects the "Yes" option in column I of the Miranda sheet, the first thing this script needs to do is pull the values of the variables at the top of the for loop within the same row that the user selected "Yes." Then, I'm pushing that data to a custom object to be called later.

It's apparent to me, that I'm doing it wrong. There's, at least, something wrong with my loop. What have I done? :)

EDIT: After reviewing the suggestion by VyTautas, here's my attempt at a working loop:

for (var k = 0; k < dataMir.length; k++) {
            var mirColI = dataMir[k][8];
            var mirRefDesc = dataMir[k][2];
            var mirAdminActions = dataMir[k][3];
            var mirDates = dataMir[k][4];
            if (mirColI === "Yes") {
              var activeRowMir = mirColI.getActiveSelection.getRowIndex();
              //Pull selected values from the active row when Yes is selected
              var mirRefDescRange = getMirSheet.getRange(activeRowMir, mirRefDesc);
              var mirRefDescValues = mirRefDescRange.getValues();
              var mirAdminActionsRange = getMirSheet.getRange(activeRowMir, mirAdminActions);
              var mirAdminActionsValues = mirAdminActionsRange.getValues();
              var mirDatesRange = getMirSheet.getRange(activeRowMir, mirDates);
              var mirDatesValues = mirAdminActionsRange.getValues();
              var mirHoldingArray = [mirRefDescValues, mirAdminActionsValues, mirDatesValues];
              //Push mirHoldingArray values to holdingData
              holdingData.refDesc = mirHoldingArray[0];
              holdingData.adminActions = mirHoldingArray[1];
              holdingData.dates = mirHoldingArray[2];
            }
        }

解决方案

Where did all that whitespace go in the actual script editor? :D

You already correctly use .getValues() to pull the entire table into an array. What you need to do now is have a for loop go through dataMir[k][8] and simply fetch the data if dataMir[k][8] === 'Yes'. I also feel that it's not quite necessary to use for (var k in dataMir) as for (var k = 0; k < dataMir.length; k++) is a lot cleaner and you have a for loop that guarantees control (though that's probably more a preference thing).

You can also reduce the number of variables you use by having

holdingData.selectedData = mirData[k]
holdingData.refDesc = mirData[k][2] //I assume you want the 3rd column for this variable, not the 4th
holdingData.adminActions = mirData[k][3] //same as above

remember, that the array starts with 0, so if you mirData[k][0] is column A, mirData[k][1] is column B and so on.

EDIT: what you wrote in your edits seems like doubling down on the code. You already have the data, but you are trying to pull it again and some variables you use should give you an error. I will cut the code from the if, although I don't really see why you need to both get the active sheet and sheet by name. If you know the name will be constant, then just always get the correct sheet by name (or index) thus eliminating the possibility of working with the wrong sheet.

   var titleMirRows = 1; // First row of data to process
   var numRowsMir = getMirSheet.getLastRow(); // Number of rows to process

// Fetch the range of cells A2:Z506
   var dataRangeMir = getMirSheet.getRange(titleMirRows + 1, 1, numRowsMir - titleMirRows, 26); // might need adjusting but now it will only get as many rows as there is data, you can do the same for columns too

// Fetch values for each cell in the Range.
   var dataMir = dataRangeMir.getValues();

   for (var k = 0; k < dataMir.length; k++) {
     if (dataMir[k][7] === 'Yes') {    //I assume you meant column i
       holdingData.refDesc = dataMir[k] //this will store the entire row
       holdingData.adminActions = dataMir[k][3] //this stores column D
       holdingData.dates = dataMir[k][4] //stores column E
     }
   }

Double check if the columns I have added to those variables are what you want. As I understood the object stores the entire row array, the value in column called Administrative Actions and the value in column Dates/Periods if Applicable. If not please adjust accordingly, but as you can see, we minimize the work we do with the sheet itself by simply manipulating the entire data array. Always make as few calls to Google Services as possible.

这篇关于Google应用程序脚本 - 使用for循环根据条件从范围中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！