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;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With