Return next element from an array in googlesheets using scripts

0
votes

I have a list of names in a google sheet. I'm trying to create a function in google apps scripts that outputs the next name in the list to a different cell in the spreadsheet. Once I've returned each name, I want to go back to the beginning.

I've tried to use a for loop as well, but then the script just loops through every item, and I end up with the just the last item being returned. 

   function returnNextName() {
       var nameList =
       SpreadsheetApp.getActiveSpreadsheet().getSheetByName("My 
       Homeroom").getRange(2, 1, 22).getValues();

       var outputCell = 
       SpreadsheetApp.getActiveSpreadsheet().getSheetByName("My 
       Homeroom").getRange(1, 4);

       var i = 0; 
       i = i + 1;
       i = i%nameList.length;

       var nextName = outputCell.setValue(nameList[i]);
     }

My goal is that every time I run the function, I will get the next name in the list. However, I only ever get the first name.

1
arraysgoogle-apps-scriptgoogle-sheets
Use PropertiesService - TheMaster

1 Answers

3
votes

Every time you call the script you redefine

       var i = 0; 
       i = i + 1;
       i = i%nameList.length;

So your element nameList[i] will be always the same.

To avoid this, you need to use PropertiesService, as suggested by TheMaster.

ScriptProperties allows you to store the last index in the script properties and retrieve and modify it every time you use the script.

Sample:

    if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
       PropertiesService.getScriptProperties().setProperty('i', 0);
      }   
    var i = Number(PropertiesService.getScriptProperties().getProperty('i'))%nameList.length;
    outputCell.setValue(nameList[i][0]);
    i++;
    PropertiesService.getScriptProperties().setProperty('i', i);