Simon Breton Simon Breton - 2 months ago 6
Javascript Question

Scrap data from one column and pass result to the column next to it

I've the following data scraping script (working with Parse library) :

function getData() {
var sheet = SpreadsheetApp.openById('my_id').getSheetByName('Sheet2');
var urls = sheet.getDataRange().getValues();

for(var i = 0; i < urls.length; i++) {
var sheet = SpreadsheetApp.openById('my_id').getSheetByName("Sheet5"); // insert Spreadsheet Id and Sheet name
var url = urls[i]
var fromText = '<span class="nb-shares">';
var toText = '</span>';
var content = UrlFetchApp.fetch(url).getContentText();
var scraped = Parser
.data(content)
.from(fromText)
.to(toText)
.build();
sheet.appendRow([ url,scraped ]);
}
}


It's working perfectly however I've hard time dealing with the outpout. I'm taking my url from the first column of sheet2 so basically
A1:A
and I would like to pass the response of my script in the column
B1:B
in the same sheet.

appendRow
just copy the value in the same column but in the rows under my url list.

Answer

You don't need to call sheet twice, and I see that one is sheet2 and the other is sheet5. Is that correct or do you just want it on sheet2 like you said? If just sheet2, delete the var sheet in the for loop and then try sheet.getRange(i+1,2).setValue(scraped) This should work for what you are doing, but your script will run a lot faster if you put the response in an array and then set that array to the sheet after. Anytime a script communicates with the sheet it takes time, this is why we try to mitigate the times we call the sheet.

To do this:

function getData() {
  var sheet = SpreadsheetApp.openById('my_id').getSheetByName('Sheet2');
  var urls = sheet.getDataRange().getValues();
  var myNewArray = [];
  for(var i = 0; i < urls.length; i++) {

     var url = urls[i]
     var fromText = '<span class="nb-shares">';
     var toText = '</span>';
     var content = UrlFetchApp.fetch(url).getContentText();
     var scraped = Parser
                    .data(content)
                    .from(fromText)
                    .to(toText)
                    .build();
     myNewArray.push([scraped]);
}
 sheet.getRange(1,2,myNewArray.length,1).setValues(myNewArray);
}

This is a much faster approche and you will not run into the 6 minute limit google has on its scripts.

Two different approaches, but if all you are trying to do is put the response on the side of the URLs you can use the code I showed you in the first part.