CODEWITHSUNDEEP
arraysstringgoogle-apps-scriptgoogle-sheets
Ian Propst-Campbell
Ian Propst-Campbell

Reputation: 158

How do I retrieve the values associated with an array based on a string name of that array?

I'm a school teacher trying to develop a math assessment data spreadsheet. To make it more automated, I'm writing a Google Apps Script that retrieves the math standards for a module based on the name of that module. I want my script to:

  1. Read the string name of the module (i.e. "Module_1") from a cell in one sheet ("Active Data Set")
  2. Find the standards associated with the name of that module in a different sheet ("Standards")
  3. Paste the standards into a given range (standRange) in the original sheet

I know I could probably have achieved the intended result using a data validation and named ranges, but I would like to do this programatically. So I assigned the standards from each module to a variable, and then placed those variables within an array(Modules). My script was supposed to loop through the array and retrieve the standards based on the name of the module I was looking for. I recognize that such an approach won't work because I'm essentially comparing a string to a variable name. 

function standardSelect() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var entrySheet = ss.getSheetByName("Active Data Set");
  entrySheet.getRange("E2").copyValuesToRange(entrySheet, 2, 2, 1, 1); //this is the name of the Module I want to pull standards for 
  var moduleName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active Data Set").getRange("E2").getValue();
  var standRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active Data Set").getRange("B2:D2"); //this is the place where I want to paste the standards 


  var stand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Standards");

  var Module_1 = stand.getRange("D5:F5").getValues(); //These are the standards associated with each module 
  var Module_2 = stand.getRange("J5:L5").getValues();
  var Module_3 = stand.getRange("O5:Q5").getValues();
  var Module_4 = stand.getRange("R5:T5").getValues();
  var Module_5 = stand.getRange("U5:V5").getValues();


  var Modules = [Module_1, Module_2, Module_3, Module_4, Module_5];
  var ModuleNames = ["Module_1", "Module_2", "Module_3", "Module_4", "Module_5"];

  for (var i = 0; i < Modules.length; i++) {
    if (moduleName == Modules[i]) {
      standRange.setValues(Modules[i])
    }
  }

}

I thought if I made another array with the names of all the modules, I could some how connect them, but I don't really know how to do that. I essentially need to call a variable inside my script based on a string inside my spreadsheet. Is there a way to do this involving key value pairs? ScriptProperties service? I'm a little stuck, so any ideas would be appreciated!

Upvotes: 0

Views: 55

Answers (1)

Ian Propst-Campbell
Ian Propst-Campbell

Reputation: 158

Here is the solution: 

var Modules = [Module_1,Module_2, Module_3,Module_4,Module_5]; 
var ModuleNames = ["Module_1","Module_2","Module_3","Module_4","Module_5"];

for(var i = 0; i< Modules.length; i++){
  if(moduleName==ModuleNames[i]){standRange.setValues(Modules[i])}
}

Upvotes: 1

Related Questions