How can I use dynamic variable numbers in a loop?

Question

I created a little tool that allows us to use a G sheet as the source for a G doc template. Essentially, it takes the values from the G sheet and replaces the placesholders in the template with the respective values.

Here I am declaring the variables:

  var MB_1 =  ws.getRange("C1").getValue();

And here I am replacing the document's body text with the respective values:

  var docBody = DocumentApp.openById(createdFileId).getBody();
  docBody
  .replaceText("{mbd_1}", MB_1)

The thing is that I have 300 variables and I want to avoid having something like this:

var docBody = DocumentApp.openById(createdFileId).getBody();
  docBody
  .replaceText("{mbd_1}", MB_1) 
  .replaceText("{mbd_2}", MB_2) 
  .replaceText("{mbd_3}", MB_3) 
  .replaceText("{mbd_4}", MB_4) 
  .replaceText("{mbd_5}", MB_5) 
  .replaceText("{mbd_6}", MB_6) 
  .replaceText("{mbd_7}", MB_7) 
  .replaceText("{mbd_8}", MB_8) 
  .replaceText("{mbd_9}", MB_9) 
  .replaceText("{mbd_10}", MB_10)

So, long story short: How can I loop that replaceText part for 300 variables? I've seen solutions with "this." but I couldn't figure how to use it in this context.

Your support is very much appreciated.

Are they in a same column? Show how you declare other MB variables. — TheMaster
– TheMaster, Commented Jul 26, 2022 at 13:48
You probably want to take a look at this accepted answer: stackoverflow.com/a/14991272/1101930. When iterating over the array you can limit the code for each cell per column — Jayr
– Jayr, Commented Jul 26, 2022 at 13:54

Wicket · Accepted Answer · 2022-07-26 18:15:10Z

1

Instead of using a variable for each single value, and reading one value at a time, as it's being done in

var MB_1 =  ws.getRange("C1").getValue();

read all the values at once

var MB = ws.getDataRange().getValues();

Note: To get the values as they are formatted in Google Sheets, instead of getValues use getDisplayValues().

then use Array indexes as follows

docBody
  .replaceText("{mbd_1}", MB[0][2])

Depending on your spreadsheet structure, you could take it further, i.e. using a loop (assuming that all the values are in Column C), like the following:

MB.forEach((row,i) => docBody.replaceText(`{mbd_${i + 1}}`, MB[i][2]))

References

Template literals

edited Jul 26, 2022 at 18:15

answered Jul 26, 2022 at 13:55

Wicket

39.6k9 gold badges81 silver badges201 bronze badges

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

6 Comments

Alain Over a year ago

Hola Rubén Thank you for your swift answer. I have two question regarding your solution: a) Depending on the value type I have to format it differently. For currency values I use "var MB_28 = formatStringCurrency(ws.getRange("C28").getValue());" for example. Date and percentage are other types. How can I individually format them? I don't see a way to identify the different formats without assigning the formatting functions to the respective variables individually. b) I would need to have that mbd_1 variable dynamic as well to match the MB variable that it is replace with. Thanks!

Wicket Over a year ago

Regarding the first question, a) apply the required formats in Google Sheets, then instead of getValues use getDisplayValues. b) to handle strings as mbd_1 dynamically I already suggested to use <code>{mbd_${i + i}}</code> P.S. there was a misplaced `, fixed and added a reference.

Wicket Over a year ago

The backticks are not shown correctly in my previous comment, the dynamic mbd_1 should be shown as `{mbd_${i + i}}`

Wicket Over a year ago

@TheMaster You are right (sorry... I should use my glasses)

Alain Over a year ago

Splendid, that this the job! Thanks a lot!

|

Collectives™ on Stack Overflow

How can I use dynamic variable numbers in a loop?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related