Automate & Loop ImportXML in Google Sheets

Question

I'm using this script to automate ImportXML with a trigger in Google Sheets. It works perfectly for 1 URL cell reference (B2 in this ex) and returns the result in D2

    function getData() {
  var queryString = Math.random();

  var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('B2').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A3').getValue() + '")';
  SpreadsheetApp.getActiveSheet().getRange('D2').setValue(cellFunction1);

 }

I need it to loop and return the result for all cells in the "B:B" range (ideally none empty ones only) and post the result in the corresponding cell in the "D:D" range.

Is it possible, or will I have to resort to writing the script for each row I want data from?

EvSunWoodard EvSunWoodard · Accepted Answer · 2016-07-13T15:24:04

Sheet.getDataRange() automatically gets a rectangular range of all of your cells with values.

var data = sheet.getDataRange().getValues(), for instance allows you to read your cells with this format.

data[0][0] // This is the value of your first cell.

So, add data[1].length, and you get your number of columns. Data.length is your number of rows. Just iterate through all your data with two for loops.

for (var i in data) {
  for (var j in data[i]) {
    // Now you can use your setters.
  }
}

Enjoy

Automate & Loop ImportXML in Google Sheets

1 Answers