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, Jul 26 '22 at 13:48
You probably want to take a look at this accepted answer: https://stackoverflow.com/a/14991272/1101930. When iterating over the array you can limit the code for each cell per column — Jayr, Jul 26 '22 at 13:54

Rubén · Accepted Answer · 2022-07-26T18:15:10.263

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 '22 at 18:15

answered Jul 26 '22 at 13:55

Rubén

34,714
9
70
166

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! – Alain Jul 26 '22 at 14:01
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 `{mbd_${i + i}}` P.S. there was a misplaced `, fixed and added a reference. – Rubén Jul 26 '22 at 14:14
The backticks are not shown correctly in my previous comment, the dynamic mbd_1 should be shown as \`{mbd_${i + i}}\` – Rubén Jul 26 '22 at 14:27
@TheMaster Because the OP's placeholders are 1 based instead of 0 based – Rubén Jul 26 '22 at 16:35
1

@TheMaster You are right (sorry... I should use my glasses) – Rubén Jul 26 '22 at 18:16
1

Splendid, that this the job! Thanks a lot! – Alain Jul 27 '22 at 08:50

How can I use dynamic variable numbers in a loop?

1 Answers1