[mail-merge] represent Google sheet formatting

[rsignell-usgs]

mail-merge is a cool tool, and will save me a bunch of time. This isn't a huge deal, but would it be possible to represent the formatting in the Google sheet?

For example, I'm working on sending a membership list for a community garden what their fee is, and although I've got "your annual plot fee is {{Fee}}" and the fee is listed in the sheet as $34.30, the email comes through as "your annual plot fee is 34.3".

I could workaround this a bit in the email by doing "your annual plot fee is ${{Fee}}" but I'd still have the issue of dropping the final digit if it's a zero.

I'm hoping there is a better solution.

[rsignell-usgs]

@mhawksey is this a realistic enhancement request or is the formatting in the sheet not accessible through the API?

[mhawksey]

@rsignell-usgs I can see how this would be a useful feature as both number and date formats are probably a common usecase. There is actually a relatively easy fix that uses the Advanced Sheets Service which returns formatted values. To use this you would need to enable the Sheets service and instead of:

var data = dataRange.getValues();

use:

var data = Sheets.Spreadsheets.Values.get(SpreadsheetApp.getActive().getId(), 
                                          dataRange.getA1Notation()).values;

a little further down in the code you'd also have to change

o[k] = values[i];

to

o[k] = values[i] || "";

Alternative just copy this modified version of the solution which has the code changes and Sheets Advanced Service enabled.

Let me know if it works and I can make a pull request

[AveryTimm]

I tried this for a sheet that had carriage returns in some of the cells...it returns "String contains control character". Any way around that?

[mhawksey]

@AveryTimm adding:

variableData = variableData.replace(/\n/g, '<br>');

after variableData is declared appears to work

I've updated the modified solution in https://github.com/gsuitedevs/solutions/issues/107#issuecomment-584849047 to include this mod

[AveryTimm]

It works! Thank you...I was just about to find all the ones with returns and send them manually.

I notice that this version will put "NOT_FOUND" in fields that are blank, which many of mine are. It's easy enough to find and replace the NOT_FOUND's, but if they don't need to be there it would be better to leave the original empty cells empty.

Thank you...that saved a lot of time.

[mhawksey]

@AveryTimm ... I see what you mean about NOT_FOUND - I've added a hacky tweak to the code shared in this thread to avoid that

[mhawksey]

Sheet cell formats picked up in https://github.com/gsuitedevs/solutions/pull/126