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 · Accepted Answer · 2016-07-13 15:24:04Z

2

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

answered Jul 13, 2016 at 15:24

EvSunWoodard

1,2681 gold badge10 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ZZZ Over a year ago

Thanks a lot for your help! However, I''m a bit new to this :$ Would you mind explaining a bit more or helping me modify the script?

Collectives™ on Stack Overflow

Automate & Loop ImportXML in Google Sheets

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related