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

问题描述

我正在学习使用Google Spreadsheets的Google Apps脚本。

我在一列中有一个URL列表，我想编写一个脚本从每个URL获取标题元素并将其写入相邻的单元格中。我根据以下脚本为一个特定单元格完成了这个工作：
$ b

  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）; 
返回标题; 
} 
  

获取G3中的URL，解析它，拉取元素并写入输出到H3中。

现在我有了这个基本的构建块，我想循环整个G列并将输出写入相邻的单元格，但是我卡住了。任何人都可以指向正确的方向吗？

解决方案

可能看起来像这样：

  function getTitles（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（url_list）; 
 var url = sheet.getRange（G3：G）。getValues（）; 
 var titleList = []，newValues = []，
 response，doc，title; 
 $ b $ 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）; 
 
 //一次设置新的列值！ 
 sheet.getRange（H3）。offset（0，0，newValues.length）.setValues（newValues）; 
返回titleList; 
} 
  

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

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

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

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?

解决方案

May look something like this:

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屋！