Dynamic variable names with string in google script

Question

Currently, I have two functions that perform the same thing, once for me (let's say H) and once for my wife (W).

What the function does is the following: select the correct spreadsheet, and then fetch some values in the spreadsheet to define 1/ a question ; 2/ a message and 3/ a subject. Later on I will make an email out of those.

Here is the code for H (variables row and loopsize have been defined before):

  ss.setActiveSheet(ss.getSheetByName("question_H"));
  var sheet_H = SpreadsheetApp.getActiveSheet();

  var question_H = sheet_H.getRange(row,6).getDisplayValue();

  var message_H = "";


  for (var i=1; i<=loopsize; i++) {
     var year = Number(startyear)+i;
     var answer = sheet_H.getRange(row,7+i).getDisplayValue();
    message_H=message_H 
    + year + ':' + '\n'
    + answer + '\n\n';
  }

  var subject_H = question_H;

I would like to know if I can make a loop so that I don't have to write the same code twice with H and W. After doing some research I came up with the following (this is not the same code, but just illustrates the solution I found):

 var names = ["H","S"];

  var message_H = "";
  var subject_H = ""; 

  var message_W = "";
  var subject_W = ""; 

  for each (var name in names){

  eval("message_"+[name] + " = "  + "'"+ name + "'")  ; 
  eval("subject_"+[name] + " = "  + "'"+ name + "'")  ;   


   }

However I find the use of eval to be very cumbersome. Is there any simpler way to do this?

Thanks for your help.

===============================EDIT======================================

Here is what I came up with following Cooper's suggestion. It is now working, although I have to use eval once, which is a bit disappointing.

Also, can anyone explain why the variables row, startyear and loopsize had to be defined in the function getQA rather than in mainQA? I would have thought that if they are defined in mainQA they could then be used in getQA, since getQA is called from mainQA. IS that wrong?

function mainQA() {


  getQA("h");
  getQA("w");

}


function getQA(type){
  var p=type;

  var ss=SpreadsheetApp.getActive();
  var help_sheet=ss.getSheetByName("help");

  var row = help_sheet.getRange(3,2).getDisplayValue(); 
  var startyear = help_sheet.getRange(4,2).getDisplayValue();
  var loopsize = help_sheet.getRange(5,2).getDisplayValue();

  var email_w = '[email protected]';
  var email_h = '[email protected]';

  var email = eval("email_"+p);


  var sh=ss.getSheetByName('question_' + p);

  var question = sh.getRange(row,6).getDisplayValue();

  var message = "";

  for (var i=1; i<=loopsize; i++) {
     var year = Number(startyear)+i;
     var answer = sh.getRange(row,7+i).getDisplayValue();
    message=message 
    + year + ':' + '\n'
    + answer + '\n\n';
  }

  var subject = 'Q&A: ' + question;
  message = message 
  +  'What is your answer for today? \n'
  +'link to a google form';


  MailApp.sendEmail(email,subject, message);

}

Cooper · Accepted Answer · 2018-01-17 03:36:54Z

2

//You can create a function with a parameter. If you don't supply the 'W' then it defaults to 'H';

function getQMS(type){
  var p=type||'H';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('sheet_' + p);
  var row={'H':1,'W':2}; //whatever you want
  var col={'H':1,'W':2}; //again whatever you want
  var q=sh.getRange(row[p],col[p]).getValue();
  var m='';//not defined
  var s='';//not defined
  var QMS={Question:q,Message:m,Subject:s};
  return QMS;
}

answered Jan 17, 2018 at 3:36

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Lejeune Over a year ago

Thanks Cooper, I have something running using your suggestion. I just had to use eval once unfortunately. New code is in my original message.

Cooper Over a year ago

You could do something like this emailObj={‘H’:’[email protected]’,’W’:’[email protected]’} then email=emailObj[p];

Tim M. · Accepted Answer · 2020-07-31 20:20:01Z

1

I was struggling to find an answer to use on a Google Sheets script, and I found an easy solution:

function SetVar() {
  var VarName = [];

  for (var x = 0; x < 5; x++) {
      VarName[x] = x; //or any other value
  }

  Browser.msgBox(VarName[1])
  Browser.msgBox(VarName[2])
  Browser.msgBox(VarName[3])
  Browser.msgBox(VarName[4])
  Browser.msgBox(VarName[5])
}

Notes

You will be able to name variables (with numbers) and use them for many things.
In my case, I've set a general variable and use the split command to get the exact position (number) of the VarName[x] that I want to use.

edited Jul 31, 2020 at 20:20

Tim M.

54.5k14 gold badges126 silver badges167 bronze badges

answered Jul 31, 2020 at 18:39

Zadir Júnior

111 bronze badge

Collectives™ on Stack Overflow

Dynamic variable names with string in google script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related