CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
firenemus
firenemus

Reputation: 165

Looping through a Google App script and setting multiple nonconsecutive values

This was difficult to title without examples and context. Here goes...

I have a Google app script which searches through a column of student ids (column A on the compiledDATA sheet) and then sets a value (an award) in column B of the same row. This works fine for a single student id, but I need the script to loop and set the same award value for all of the students in the GroupAwardIDs range which is located on a separate sheet called Group Awards.

Here's a link to my sample spreadsheet.

The values to be set are nonconsecutive, and in actual use there may be over a thousand to be set at a time.

How can I achieve this in a quick and efficient way without running into quota issues?

Here's the script (please excuse all the comments - it helps me keep track):

function AwardGroup() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var StarLog = sheet.getSheetByName("compiledDATA");
var GroupAward = sheet.getRangeByName("GroupAward").getValue();
var GroupAwardIDs = sheet.getRangeByName("GroupAwardIDs").getValue(); // THESE ARE THE IDS OF STUDENTS WHO WILL RECEIVE THE AWARD. HOW DO SET VALUES FOR ALL AND ONLY THESE IDS?
var IDs = sheet.getRangeByName("StudentIDs").getValues(); // all of the student IDs

for (var i = 0; i < IDs.length; i++) {
if (IDs[i] == "123461") { // THIS WORKS IF HARDCODE A SINGLE ID  
    var rowNumber = i+3; // find row and add 3 to compensate for GroupAward range staring at row 3
    var StarLogCurrent = StarLog.getRange("B"+rowNumber).getValue(); // locates students award log cell using A1 notation
    var appendAward = GroupAward.concat(StarLogCurrent); // prepends new award to previous awards

    StarLog.getRange("B"+rowNumber).setValue(appendAward); //write new star log

    }
  }
}

Upvotes: 1

Views: 1609

Answers (1)

Tanaike
Tanaike

Reputation: 201643

You want to put GroupAward ("'Group Awards'!B3") to the column "B" of "compiledDATA" with the same row, when StudentIDs ("compiledDATA!A3:A1000") and GroupAwardIDs ("'Group Awards'!B7:B1002") are the same. If my understanding is correct, how about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points :

  • Retrieve all GroupAwardIDs.
  • Remove empty elements in GroupAwardIDs.
  • Search IDs using GroupAwardIDs and put GroupAward when the IDs is the same with GroupAwardIDs.
  • Put the values with GroupAward.

Modified script :

Please modify as follows.

From : 
var GroupAwardIDs = sheet.getRangeByName("GroupAwardIDs").getValue(); // THESE ARE THE IDS OF STUDENTS WHO WILL RECEIVE THE AWARD. HOW DO SET VALUES FOR ALL AND ONLY THESE IDS?
var IDs = sheet.getRangeByName("StudentIDs").getValues(); // all of the student IDs

for (var i = 0; i < IDs.length; i++) {
  if (IDs[i] == "123461") { // THIS WORKS IF HARDCODE A SINGLE ID  
    var rowNumber = i+3; // find row and add 3 to compensate for GroupAward range staring at row 3
    var StarLogCurrent = StarLog.getRange("B"+rowNumber).getValue(); // locates students award log cell using A1 notation
    var appendAward = GroupAward.concat(StarLogCurrent); // prepends new award to previous awards
    StarLog.getRange("B"+rowNumber).setValue(appendAward); //write new star log
  }
}
To : 
var GroupAwardIDs = sheet.getRangeByName("GroupAwardIDs").getValues(); // Modified
var IDs = sheet.getRangeByName("StudentIDs").getValues();

// I modified below script.
GroupAwardIDs = GroupAwardIDs.filter(String);
var res = IDs.map(function(e){
  return GroupAwardIDs.filter(function(f){
    return f[0] == e[0]
  }).length > 0 ? [GroupAward] : [""];
});
sheet.getRange("compiledDATA!B3:B1000").setValues(res);

If I misunderstand your question, please tell me. I would like to modify it.

Edit :

You want to add GroupAward to the original values at column B. I understood what you want to do like this. If my understanding is correct, please modify to as follows. In this sample, I used ", " as the delimiter.

var GroupAwardIDs = sheet.getRangeByName("GroupAwardIDs").getValues(); // Modified
var IDs = sheet.getRangeByName("StudentIDs").getValues();

// I modified below script.
var columnB = sheet.getRange("compiledDATA!B3:B1000");
var valColB = columnB.getValues();
GroupAwardIDs = GroupAwardIDs.filter(String);
var res = IDs.map(function(e, i){
  return GroupAwardIDs.filter(function(f){
    return f[0] == e[0]
  }).length > 0 ? [valColB[i][0] ? GroupAward + ", " + valColB[i][0] : GroupAward] : [valColB[i][0]]; // Modified
});
columnB.setValues(res);

Upvotes: 1

Related Questions