返回行号匹配错误 [英] Return row number is match error

问题描述

我在一张纸上有几张桌子，我需要编写一个脚本来每天发送一封电子邮件.它今天搜索的脚本约会，将其匹配到我的表中并返回列号，然后我在第一列(A)中查找总计"，找到后返回行号.获得行号和列号后，返回当天的总计值.我对JavaScript并不了解，并且对数组(仍在学习)感到困惑.到目前为止，只要该工作表上只有一个表，我的脚本就可以很好地工作，但是工作表上将有50多个表，每个表的末尾都有一个总计.我拥有的公式将找到总计，但将所有总计(行号)作为字符串返回.我需要的只是获得第一个总计(行数).我希望这一切都有道理.

I have some tables in one sheet and I need to make a script to send an email everyday. The script it search today date, match it into my table and return column number, then I looks for Total on the first column (A) and once found it, return row number. Once I have row number and column number, return Total value for that day. I'm not advanced with JavaScript and I'm struggle with arrays (still learning). The script I have so far is working very good as long I have only one table on that sheet, but on the sheet will be over 50 tables, each one will have a Total at the end. The formula I have will find Total, but will return all Totals (row numbers) as Strings. What do I need is to get just the first Total (row number). I hope it all make sense.

我已附上图片以提出想法，到目前为止，我的脚本是:

I have attached an image to make an idea and my script I have so far:

function getTodaysTotal() {
 function toDateFormat(date) {
try {return date.setHours(0,0,0,0);}
catch(e) {return;}
}

var values = SpreadsheetApp
  .openById("ID")
  .getSheetByName("Q3 - W27 - 39")
  .getDataRange()
  .getValues();


for (i in values){
 if (values[i][0]=='Total'){
var nr = i;

   Logger.log(nr); // will return two values (41 - first total and 
   104 second total ... if you add more Total it will return all rows 
   numbers that contain word Total

 }
}

var today = toDateFormat(new Date());
var todaysColumn = 
values[5].map(toDateFormat).map(Number).indexOf(+today);
var output = values[nr][todaysColumn];
 //     Logger.log(output);
var emailDate = Utilities.formatDate(new Date(today),"GMT+1", 
 "dd/MM/yyyy");

这只是第一个表，但在此表下将有更多表，每个表都有一个总计.

This is just the first table, but there will be more under this one and each one will have a Total.

谢谢！

亲切的问候！

推荐答案

您应该在循环之外声明nr，因为您将使用该值.

You should declare nr outside of your loop since you will use the value.

var nr = 0;

由于要读取数组，因此应使用数组长度进行循环

Since you are reading an array, you should use the array length for you loop

for (var i=0; i<values.length; i++){
  if (values[i][0]=='Total'){
    nr = i;
    Logger.log(nr); 
    break; // this will stop at the first match
  }
}

获得总计的字符串后，可以通过调用以下函数将其转换为数字.

Once you get your total as a String, you can convert it to a number by calling the following function.

var string = values[a][b];
var num = Number(string);

这篇关于返回行号匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！