Single-Cell Character Limit and FormEmailer Settings

[Greg Pearl]

Hello, fellow Googlers,

I too have a fond appreciation for Mr. Henrique Abreu's lovely FormEmailer script, but since upgrading many of my workbooks to the new Google Sheets and installing the latest version of FormEmailer, I've received the following Google Drive error while attempting to save the settings:

Error encountered: Your input contains more than the maximum of 50000 characters in a single cell.

It's true that some of my email templates exceed 6,000 words; however, this wasn't an issue with the old spreadsheets, so I assume that the "50,000 characters in a single cell" limit is a new restriction, as the FormEmailer script I'm using has not changed. Might there be a way to tweak Mr. Abreu's newest script to allow for the saving of FormEmailer settings across multiple cells rather than requiring all settings to be saved into a single cell?

Thanks in advance for any insight on the matter!

Greg

[Henrique Abreu]

Hi Greg,

Yes, that a new cell limit of the new sheets, nothing to do with FormEmailer there. But I see your point, the cell that has more than 50000 chars is where FormEmailer store its settings.

It's surely possible to change it to save the settings elsewhere or spread it in multiple cells, but it's not that straightforward to code it.

I think an easy workaround you can try is write the major part of your email body (e.g. a long plain text section) in a cell in row 2, as a formula, then have FormEmailer use it as a placeholder, like any other column. And set the formulas location parameter to copy it down and possibly change the closure mode to "clear" just so there isn't a lot of repeated cells in your sheet.

Please take a look on these other questions to see how to use the formulas location parameter:

Getting e-mail from VLOOKUP function

Error sending email: Exception: Invalid email: #N/A

Kind regards,

Henrique Abreu

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.
Visit this group at http://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

[Greg Pearl]

That's a clever idea, sir! In the long run, it may not be ideal, as some of my emails have placeholders evenly distributed throughout the text. So, the email body would need to be written in pieces as several formulas. However, your workaround is a great way to keep things running in the interim.

Thank you for taking the time to answer my question so thoughtfully!

Greg

[Henrique Abreu]

You can build the whole email text using formulas in the spreadsheet. By concatenating the placeholders yourself. Surely more complicated than using the built-in feature, but is possible nonetheless.

Henrique Abreu

--

[Greg Pearl]

The good news is that you were right and everything worked!

One wrinkle I discovered in the process, though, that I wanted to include here for anyone else curious: The method I chose to concatenate placeholders was...

="Hi "&AR2&",<br><br>Thanks for taking the time to submit a form..."

Essentially, I just replaced the FormEmailer's placeholders (e.g. #First Name#) with the corresponding column names from my spreadsheet (e.g. &AR2&). It was easy work.

The tricky part came when my email template contained double quotation marks. In other words, code like...

This is simply an "icebreaker" report. If any of the information below is unclear, please reference the online resources <a href="google.com/analytics">here</a>.

...had to become...

This is simply an "&CHAR(34)&"icebreaker"&CHAR(34)&" report. If any of the information below is unclear, please reference the online resources <a href="&CHAR(34)&"google.com/analytics"&CHAR(34)&">here</a>.

...in order to avoid a parse error in the spreadsheet. Again, other than that, this workaround works splendidly. Thanks again for the suggestion!

Greg

[Henrique Abreu]

Thanks for your feedback Greg.

This double quotes problem is a generic spreadsheet issue, since the quotes are used to delimit the text, when you need it within the text you have to either use its code, like you did. Or, as a short, write two quotes in a sequence and it will understand you want one. e.g.

="This is simply an ""icebreaker"" report."

Regards,

Henrique Abreu

--

[Greg Pearl]

That's far easier than my method. Thanks again for the tips!