“无法读取未定义的属性'indexOf'"； [英] "cannot read property 'indexOf' of undefined"

问题描述

我正在编写一个简单的代码来遍历范围内的所有链接，从那些电子表格(链接)中收集信息，然后粘贴到循环的当前行中.

I am writing down a simple code to loop through all the links in a range, gather info from those spreadsheets (links) and paste in the current row of the loop.

function UpdateProjects()
{

//----------EDIT THIS FOR A DIFFERENT CLIENT-------------
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Projects");
  var etilastrow = sheet.getLastRow();

  var urlRange = sheet.getRange(2, 1, etilastrow);
  var urlsvalues = urlRange.getValues();
  var urls = urlRange.getFormulas();
  Logger.log(urls)

  //looping through all of the rows
  for (var i = 0; i < etilastrow; ++i) {
   currentRow = 2 + i;
  var dirturl = urls[i];
  var remove_before = dirturl[i].indexOf('",');
    var url = dirturl[i].substring(12, remove_before);

Logger.log(currentRow);
    Logger.log(url);

//-----------------Loop code below until last project-------
  var projectPCBdesignsheet = SpreadsheetApp.openByUrl(url).getSheetByName("Main");
    var lstrow = projectPCBdesignsheet.getLastRow(); 

  const vA=projectPCBdesignsheet.getRange(7,2,lstrow).getValues();//get data from project's PCB Design sheet
   
//add last edit values  
  sheet.getRange(currentRow, 4).setValue(vA[10]);
  sheet.getRange(currentRow, 5).setValue(vA[11]);    

//additional data from PCB design sheet
  sheet.getRange(currentRow, 6).setValue(vA[0]);
  sheet.getRange(currentRow, 7).setValue(vA[1]);
  sheet.getRange(currentRow, 8).setValue(vA[2]);
  sheet.getRange(currentRow, 9).setValue(vA[3]);  
  sheet.getRange(currentRow, 11).setValue(vA[5]); 
}
}

脚本将起作用.现在，从日志中，我可以看到行号和干净的URL.

Script works if I hard code an url into the variable. Right now, from the log, I can see the row number and the clean url.

问题是脚本因以下错误而停止:" TypeError:无法读取未定义的属性'indexOf'ETIUpdateProjects@ UpdateProjects.gs:18"

Problem is that the script stops with this error : "TypeError: Cannot read property 'indexOf' of undefined ETIUpdateProjects @ UpdateProjects.gs:18"

如果它向我显示currentRow和url的日志(位于"indexOf"之后)，我不知道如何在第18行发现错误.行.

I don't understand how it can find an error at row 18 if it shows me the log of currentRow and url, that come after the "indexOf" row.

推荐答案

问题:

问题1:

从第二行开始，您将在数据中额外获得一个空单元格:

Starting from the second row, you get one empty cell extra in the data:

var urlRange = sheet.getRange(2, 1, etilastrow);

这将为您提供以下内容: [[== link]，['']]

this will give you something like: [[=link],['']]

这可以通过将 etilastrow 更改为 etilastrow-1 来解决.

this can be fixed by changing etilastrow to etilastrow-1.

，并且在 for 循环中也是如此.

and also the same in the for loop.

问题2:

即使您解决了第1个问题，当前代码中的主要问题(也会产生您遇到的错误)都在以下几行中:

Even if you fix issue 1, the main issue in the current code which also produces the error you are getting is in these lines:

var dirturl = urls[i];
var remove_before = dirturl[i].indexOf('",');

请记住， urls 是 etilastrow 行的数组，但只有一个列.如果您改写代码，则所谓的呼叫实际上就是 urls [i] [i] .在第二次迭代后，当 i 变为 1 时，它将返回 undefined ，因为数据中只有一列.

Remember, urls is an array of etilastrow rows but only one column. If you paraphrase your code, what you call is essentially urls[i][i]. This will return undefined after the second iteration, when i becomes 1, because there is only one column in the data.

您的代码起作用的原因是因为您正在填充一个单元格对其进行测试，因此 urls [0] [0] 可以正常工作，但是如果添加更多URL，则第二个索引将增加到超过 0 的位置，例如 urls [0] [1] 就会丢失并出错，因为 urls 只有一列.

The reason your code works, is because you are testing it with one cell filled in and therefore urls[0][0] works properly, but if you add more URLs the second index will increase to more than 0 and this for example urls[0][1] will drop and error because urls has only one column.

要解决此问题，您可以更改:

To solve this issue you could change:

var dirturl = urls[i];
var remove_before = dirturl[i].indexOf('",');
var url = dirturl[i].substring(12, remove_before);

收件人:

var dirturl = urls[i];
var remove_before = dirturl[0].indexOf('",');
var url = dirturl[0].substring(12, remove_before);

改进:

改进1:

我建议您阅读 for 循环，因为它们在处理索引和数组时会造成混乱，并且您需要足够舒适地处理它们.使用 forEach 和 flat()可以直接使用每个网址:

I would advice you to get read of the for loops because they can be confusing when dealing with indexes and arrays and you need to be comfortable enough to handle them. Use forEach and flat() to work with each url directly:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Projects");
  var etilastrow = sheet.getLastRow();

  var urlRange = sheet.getRange(2, 1, etilastrow-1);
  var urlsvalues = urlRange.getValues();
  var urls = urlRange.getFormulas();


  urls.flat().forEach((r,i)=>{
    let currentRow = i + 2;
    let remove_before = r.indexOf('",');
    let url = r.substring(12, remove_before);
    console.log(url)
    console.log(currentRow)
    // put here the rest of your code
  })

改进2:

从Google Apps脚本的角度来看，多次调用 setValue 以及在 for 循环中调用都可能会花费很多性能.根据最佳做法，正确的做法是将值存储在数组中，然后在for循环之外使用 setValues .这样，您只需使用一次 setValues .

From Google Apps Script point of view calling setValue many times but also within a for loop can cost a lot of performance. According to Best Practices the proper way to do that, is to store the values in an array and then use setValues instead outside of the for loop. In this way, you use only setValues once.

我可以尝试优化代码的这一部分，但是答案将无济于事，因为已经有很多关于此优化的文章，但您的代码也应与我指出的更正一起工作.

I could try to optimize that part of your code but the answer is not going to be helpful enough because there are already many posts regarding this optimization but also your code should work with the corrections I indicated.

这篇关于“无法读取未定义的属性'indexOf'"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！