如何循环单元格范围并在相邻列中设置值 [英] How to loop range of cells and set value in adjacent column

问题描述

我正在学习用于 Google 电子表格的 Google Apps 脚本.

I'm learning Google Apps Scripts for use with Google Spreadsheets.

我在一列中有一个 URL 列表，我想编写一个脚本来从每个 URL 中获取标题元素并将其写入相邻的单元格中.我已按照以下脚本为一个特定单元格完成此操作:

I have a list of URLs in one column and I want to write a script to get the title element from each URL and write it in the adjacent cell. I have accomplished this for one specific cell as per the following script:

function getTitles() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("url_list");
  var range = sheet.getRange("G3");
  var url = range.getValue();

  var response = UrlFetchApp.fetch(url);
  var doc = Xml.parse(response.getContentText(),true);
  var title = doc.html.head.title.getText();
  var output = sheet.getRange("H3").setValue(title);

  Logger.log(title);
  return title;
}

这会在 G3 中获取 URL，解析它，拉取元素并将输出写入 H3.

This gets the URL in G3, parses it, pulls the element and writes the output in H3.

现在我有了这个基本的构建块，我想循环整个 G 列并将输出写入相邻的单元格，但我卡住了.有人能指出我正确的方向吗?

Now that I have this basic building block I want to loop the entire G column and write the output to the adjacent cell but I'm stuck. Can anyone point me in the right direction?

推荐答案

可能看起来像这样:

function getTitles() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("url_list");
  var urls = sheet.getRange("G3:G").getValues();
  var titleList = [], newValues = [],
      response, doc, title;

  for (var row = 0, var len = urls.length; row < len; row++) {
    if (urls[row] != '') {
      response = UrlFetchApp.fetch(urls[row]);
      doc = Xml.parse(response.getContentText(),true);
      title = doc.html.head.title.getText();
      newValues.push([title]);  
      titleList.push(title);  
      Logger.log(title);
    } else newValues.push([]);
  }

  Logger.log('newValues ' + newValues);
  Logger.log('titleList ' + titleList);

  // SET NEW COLUMN VALUES ALL AT ONCE!
  sheet.getRange("H3").offset(0, 0, newValues.length).setValues(newValues);
  return titleList; 
}

这篇关于如何循环单元格范围并在相邻列中设置值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！