Commas in replaceText (Google Script)

Question

I'm writing a script that take an arrays of values in Google Spreadsheet and populate a Google Docs. Everything is working using body.replaceText. The only issues is that the text in the Google Doc appears with a comma at the end.

So if I have

%AREA_2%    text here
%AREA_3%    text here
%AREA_4%    text here

In the Docs appears: text here, (with the comma)

var tag = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
var content = sheet.getRange(1, 2, sheet.getLastRow(), 2).getValues(); 
for (var i in tag){
  body.replaceText(tag[i], content[i]+"");
}

Do you have any suggestion on how to avoid it? Thanks!

My guess is that the Google Docs API is grabbing the values from the spreadsheet like it would from a CSV file and not getting rid of the commas between values. For this you would want to iterate through your content variable and create a string manually by appending the values of the cells to a master string. — RevanProdigalKnight

Mogsdad Mogsdad · Accepted Answer · 2015-04-14T21:01:57

You are (mis)using the getValues() method, which will return a two-dimensional array (rows of cells). If we pause execution inside the for loop, here's what we have in memory:

debugger

Note that tag and content are arrays-of-arrays. The three sub-arrays in tag each have one element, while those in content have two.

So in this line...

body.replaceText(tag[i], content[i]+"");

...we have a method that is expecting two String parameters. The Javascript engine attempts (and succeeds) at providing them, even though neither tag[i] nor content[i] are Strings to start with. This is called type coercion.

The case of tag[0] is very simple; the single element array gets interpreted as a string, with no noticeable side-effects.

What about content[0]? It's a two-element array, containing "text here" and "", a blank string. The array is coerced by joining its elements together, separated by commas. So that's why you see text here,. (You would get the same result with content[i].join().)

A quick fix would be to correct the indices being used to read content:

var content = sheet.getRange(1, 2, sheet.getLastRow(), 1).getValues(); 
                                                      ^^^

Alternatively, we could ensure the code matches the data structure, and avoid relying on coercion altogether:

var tag = 0, content = 1; // Column indices
var tagset = sheet.getRange(1, 1, sheet.getLastRow(), 2).getValues();
for (var i in tagset){
  body.replaceText(tagset[i][tag], tagset[i][content]+"");
}

Commas in replaceText (Google Script)

1 Answers