如何循环 Google 电子表格列值并将结果设置在 B 列中？

我在 A 列中有一个 url 列表，我想循环 A 列并将每个 url 的状态代码设置到 B 列。我尝试了此代码，但我不断收到未找到范围在脚本编辑器窗口中？我知道我必须将行的值添加到 B 列的名称中，但我不断收到此错误。任何人都可以告诉我如何修复此错误。提前致谢。

function getStatus() {

    var queryString = Math.random();

  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getSheetByName("Sheet1"),
      urls = sheet.getRange("A2:A").getValues(),

      row, len


  for (row = 0, len = urls.length; row < len; row++) {
    if (urls[row] != '') {

        var cellFunction1 = '=HTTPResponse("' + urls[row] + '?' + queryString + '")';
  SpreadsheetApp.getActiveSheet().getRange('"B"+row+').setValue(cellFunction1);

     } 
  }

  }

function HTTPResponse( uri )
{
 var response_code ;
try {
 response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;
 }
catch( error ) {
 response_code = error .toString() .match( / returned code (\d\d\d)\./ )[1] ;
 }
finally {
 return response_code ;
 }
}

可能有更好（也更优雅）的方法来做到这一点，但至少这似乎有效：

 function getStatus() {    

  var queryString = Math.random();    

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn(); 
  var dataRange = sheet.getRange(2, 1, lastRow -1, lastColumn);
  var data = dataRange.getValues();
  var row = 0;
  var len = data.length;


  for (row; row < len; row++) {
    if (data[row] != '') {    

        var cellFunction1 = '=HTTPResponse("' + data[row][0] + '?' + queryString + '")';
        // change the following line, last number for another column
        var cell = sheet.getRange(row+2, 2);
        cell.setValue(cellFunction1);

     } 
  }    

  }    

function HTTPResponse( uri )
{
 var response_code ;
try {
 response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;
 }
catch( error ) {
 response_code = error .toString() .match( / returned code (\d\d\d)\./ )[1] ;
 }
finally {
 return response_code ;
 }
}

也许你可以从这里开始改进它。屏幕截图电子表格列