如何循环使用Google电子表格的列值并在B列中设置结果? [英] How to loop a google spreadsheet column values and set result in column B?

问题描述

我在A列中有一个网址列表，我想循环此A列并将每个网址的状态代码设置为B列.我尝试了此代码，但我一直得到未找到范围在脚本编辑器窗口中?我知道我必须将row的值添加到B列的名称中，但是我一直在收到此错误.有人可以告诉我如何解决此错误.谢谢.

i have a list of urls in column A and i want to loop this column A and set the status code for each url to column B. I tried this code but i keep getting Range not found in script editor window? i know i have to add the value of row to name of column B but i keep getting this error.could any one tell me how to fix this error .Thanks in advance.

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 ;
 }
}

推荐答案

可能有更好(更优雅)的方法，但是至少这似乎可行:

There are probably better (and more elegant) ways to do it, but at least this seems to work:

 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 ;
 }
}

也许您可以从这里进行改进. 屏幕快照电子表格列

Maybe you can go from here and improve it. screenshot spreadsheet columns

这篇关于如何循环使用Google电子表格的列值并在B列中设置结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！