Google Sheets Script For Loop Issues

Question

I am trying to iterate through the arrays and copy the values to a table. The gamesArrayList are (Home and away teams) that get copied into B2:B3 for an algorithm to get processed then C5:E6 is the area where the first games "statistics" go and then I need it to go to game 2 C7:E8, game 3 C9:E10, etc. The teams are all different. I have been working on it for 5 hours and am very stuck. Any direction will helps, thanks! (I know this is a very easy question, but I do not do this for a living.)

   function RunAlgo(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getSheets()[0];
      var gamesArrayList = s.getRangeList(['B5:B6', 'B7:B8', 'B9:B10']);
      var gamesArray = gamesArrayList.getRanges();
      var gameDataList = sheet.getRangeList(['C5:E6', 'C7:E8','C9:E10']);
      var gameData = gameDataList.getRanges();
      for (var i in gamesArray) {
      gamesArray[i].copyTo(ss.getRange('B2:B3'), {contentsOnly:true});
      testWait();
        for (var i in gameData) {
          ss.getRange('G2:I3').copyTo(gameData([i]), {contentsOnly:true});
             testWait();
      }
     }
    }

Welcome. I don't think many here do this for a living either ;-) In any event, it would help if you would please share a copy of your spreadsheet, and include an example of a successful outcome. — Tedinoz, Dec 07 '19 at 01:58

Cooper · Accepted Answer · 2019-12-07T05:07:41.403

You had a few errors in your code and I just guessed at what you wanted. But go ahead and give it a try and let me now what doesn't work. When you're designating ranges in a spreadsheet it's a good idea to specify the sheet. Also using the Spreadsheet.getSheets()[0] is not really a very good idea because it's not a unique sheet. It's the left most sheet so it changes if you move them around. Also, I only use for in loops for iterating through objects not arrays. You need to pay attention to content assist in the Script Editor it helps to tell you when your correctly connected to previously declared variables. Your gameData was not an array because the term 'sheet' was undefined. So any way I made some guesses to fix the problems hopefully that's closer to what you wanted.

function RunAlgo(){
  var ss=SpreadsheetApp.getActive();
  var s=ss.getActiveSheet();
  var gamesArrayList = s.getRangeList(['B5:B6', 'B7:B8', 'B9:B10']);
  var gamesArray = gamesArrayList.getRanges();
  var gameDataList = s.getRangeList(['C5:E6', 'C7:E8','C9:E10']);
  var gameData = gameDataList.getRanges();
  for (var i=0;i<gamesArray.length;i++) {
    gamesArray[i].copyTo(s.getRange('B2:B3'), {contentsOnly:true});
    for(var j=0;j<gameData.length;j++) {
      s.getRange('G2:I3').copyTo(gameData[j], {contentsOnly:true});
    }
  }
}

Since I don't have any access to your data I can't really debug the code. So I assumed that the part of the code that doesn't have syntax issues is correct.

Thank you so much that was the fix I was looking for. What would you use instead of "for loops" when iterating through arrays? I know am running into a problem where there is not enough time for the data to load between teams and stats being added to the table. I use a //holds processing of next script till last one has completed function testWait(){ var lock = LockService.getScriptLock(); lock.waitLock(5000); SpreadsheetApp.flush(); lock.releaseLock(); } — Avery G, Dec 07 '19 at 04:42
I said [for in](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for...in) loops and I only use them in objects. I use [for loops](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for) for arrays. [Why use for in](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for...in#Why_Use_for...in) [Why using for in loops for interating through arrays is a bad idea](https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea) — Cooper, Dec 07 '19 at 05:06

Google Sheets Script For Loop Issues

1 Answers1