How to Iterate to output data to Google Sheets with App-Script

Question

Using Google App Script, when I used Logger.log() the for loop iterates properly and I get results for each value. When I try to output this to a google sheet only the last value for each variable is output over and over again for the number of goals.length.

Any help is very much appreciated!

function listGoals() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var filterList = Analytics.Management.Goals.list(accountId, webPropertyId, profileId)
      var goals = filterList.items;
        for (var i = 0, goal; goal = goals[i]; i++) {
            var accountId = goal.accountId;
            var propertyId = goal.webPropertyId;
            var goalNumber = goal.id;
            var goalName = goal.name;

            Logger.log('accountId: ' + accountId);
            Logger.log('profileId: ' + propertyId);
            Logger.log('goal number: ' + goalNumber);
            Logger.log('goal name: ' + goalName);
            //Logger.log prints for each result

            sheet.getRange(1,1,goals.length).setValue(goalNumber);
            sheet.getRange(1,2,goals.length).setValue(goalName);
            //this only prints out the last value of goalNumber and goalName to the sheet

        }
    }

Robin Gertenbach · Accepted Answer · 2016-03-01 09:23:29Z

1

It doesn't only print the last results, it just keeps overwriting the old result with the new one. goals.length only helps if you then supply an array of arrays containing the values looking as such:

[[1, "Goal 1"], 
 [2, "Goal 2"]]

If you want to print out a list of goalNumber and goalName you need to offset the cell to write in every time. something like

sheet.getRange(1+i,1).setValue(goalNumber);
sheet.getRange(1+i,2).setValue(goalName);

To speed up the process a bit and not do two calls for every goal you can store the id name pairs as arrays within an array and do one final setValues call after the loop finishes executing.

function listGoals() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var filterList = Analytics.Management.Goals.list(accountId, webPropertyId, profileId)
  var goals = filterList.items;
  var goalsToWrite = [];

  for (var i = 0, goal; goal = goals[i]; i++) {
    goalsToWrite.push([goal.id, goal.name]);
  }

  sheet.getRange(1, 1, goals.length, 2).setValues(goalsToWrite);
}

answered Mar 1, 2016 at 9:23

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

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

1 Comment

Ryan Praskievicz Over a year ago

Thank you so much. That's exactly what I wanted. Thanks for also providing the 2nd example with the setValues() as well.

Collectives™ on Stack Overflow

How to Iterate to output data to Google Sheets with App-Script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related