How to loop range of cells and set value in adjacent column

Question

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?

Bryan P · Accepted Answer · 2016-02-17T06:42:30.687

25

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

edited Feb 17 '16 at 06:42

answered Mar 25 '13 at 14:38

Bryan P

5,031
3
30
44

1

Thank you Bryan, that is very elegant. Can I ask, you use arrays in this solution and push. Performance wise, does that have any particular advantages if you have a long number of rows? Many thanks for taking the time to post this! – needlesslosses Mar 25 '13 at 15:19
Advantage over what alternative? I pushed to 2 arrays b/c I didn't know if you needed to just return 1 (titleList) with no empty values. – Bryan P Mar 25 '13 at 17:07
Advantage over doing a loop and pushing each value as it becomes available. My (not very pretty) code was much slower so that answered my question. Thanks again! – needlesslosses Mar 25 '13 at 17:38
1

Yeah, using setValue() for each row is not the way to go. – Bryan P Mar 25 '13 at 17:43
@Bryan P if i want run =httpresponse for the url column how to write the status to adjacet colum "H"? – user1788736 Oct 11 '15 at 02:38
@user1788736 it would have been `newValues.push([title, status]);` then `sheet.getRange("H3").offset(0, 0, newValues.length, newValues[0].length).setValues(newValues);`. Start a new post if you're still having trouble. – Bryan P Feb 17 '16 at 16:05
`for (var row = 0, var len = urls.length; row < len; row++) {` syntax error. why? – Ling Loeng May 23 '18 at 17:25

How to loop range of cells and set value in adjacent column

1 Answers1

Linked