使用脚本从googlesheets中的数组返回下一个元素 [英] Return next element from an array in googlesheets using scripts

问题描述

我在Google工作表中有一个名称列表.我正在尝试在Google Apps脚本中创建一个函数，该函数将列表中的下一个名称输出到电子表格中的其他单元格.返回每个名称后，我想回到开头.

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.

我也尝试过使用for循环，但是脚本只循环遍历每个项目，最后我得到的只是最后一个返回的项目.

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.

推荐答案

每次调用脚本时都要重新定义

Every time you call the script you redefine

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

所以您的元素nameList[i]将始终相同.

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

为避免这种情况，您需要使用 PropertiesService ，如TheMaster所建议.

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

ScriptProperties允许您将最后一个索引存储在脚本属性中，并在每次使用脚本时对其进行检索和修改.

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

    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);

这篇关于使用脚本从googlesheets中的数组返回下一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！