MergeFactory

Spreadsheet merge to document & email utility

The MergeFactory is yet another email and document merge add-on designed for Google Sheets. The main features that distinguish this add-on are:

• Emails and documents are merged with the currently selected row or rows of the Google Sheet, which is a very quick and intuitive way when working with spreadsheet data. Use your mouse or trackpad to highlighting one or multiple rows and then click the desired action button in the sidebar.
• No header rows are required and row content can be merged with simple column data markers; e.g. @colA@ @colF@ etc. to insert the corresponding (column A and F) data of the selected row into the template. The current @date@ and @time@ can also be used in templates.
• Column header markers; e.g. %colA% %colD% etc. are available to insert the corresponding (column A and D) content of row #1 into the template. Header markers always assumes the first row of a sheet is the header row.
• Multiple email, document, and label templates can be created for each Google Spreadsheet with the premium version.
• Each template can be limited to a specified tabs(s) / grid(s) in order to avoid merging errors in case you are selecting rows in the wrong tab when initiating a merge.
• New Doc Merge will merge each row into a copy of the selected document template, create one multi-page document for all selected rows, and then display the created document link in the Results tab. Premium users can also select additional rows and append these merged pages into the current document with a Fill In Doc Merge. Premium users can also select additional rows, from a different tab, and a different document template, and then append these merged pages into the current document with the Fill In Doc Merge. This is a powerful feature and does require consistency in the margins, headers, and footers of the document templates.
• Simple plain text emails can be created quickly, easily, and with no html knowledge right in the sidebar. If you need more professional formatting, you can create and paste your own HTML for the ultimate in control.
• Additional email options (Cc, Bcc, From Name, Reply To, Attachments, HTML) are available to create professional quality email content.
• The Email Attachment is a references to a Google Document file. This will convert the Google Doc to a PDF and send the PDF file as an attachment to the email.
• New Label Merge will merge each selected row into a copy of the selected label template, fill each label area with merged row information, and then display the created document link in the Results tab. Users can also select additional rows and merge these rows into the current document until all the available label areas are filled. If the current document is compatible, then a user can also select additional rows, from a different tab, and a different label template, and continue to fill the current label with the Fill In Label Merge. The label merge is a premium feature.
• This add-on only utilizes Google G Suite services so your sheet data, document and email content is never shared outside of the Google environment and can not be accessed by the developer.
• Editors of a shared Google Sheet can also utilize this add-on after giving authorization and when all the required template files and required folders are also shared. A shared sheet editor will need a premium subscription in order to access more than the first email or document template for the spreadsheet. A shared sheet editor can create, edit, or delete email and document templates, so share with caution. Remember to share the folders that template are in, so copies can be created and saved in these same folders. Googles new per-file authorization for Google Drive Files means that each user in a shared spreadsheet has to use the Google Picker to either re-select a template another user has added or have used Google Picker to select that same document in another spreadsheet so that the add-on can access that file for each individual user.
• Premium subscribers can also utilize the Format Sidebar to highlight rows or columns of cells and convert the content to either Proper, Title, Sentence, Lower, or Upper case formatting. 10 and 11 digit phone numbers can be converted to the North American Number Plan format.
• Payment is not required to install or use this add-on with one saved merge template for each individual spreadsheet. A premium version is available that allows for multiple email, document, and label templates for each individual spreadsheet.

Install Permissions

Current Permissions

The MergeFactory requests the following scopes when installed. Below is an explanation of why the add-on needs each permission.

Display and run third-party web content in prompts and sidebars inside Google applications: Google provides add-ons access to the sidebar and pop-up prompts as an interface with the user.

Allow the application to run when you are not present: Google allows add-ons to schedule time or event based triggers to perform some actions. The MergeFactory does not utilized this scope and only runs through user actions.

Connect to an external source: The MergeFactory connects to our merchant provider (Stripe) in order to process payments for our premium service. This is initiated through the "Go premium" sidebar. The MergeFactory does not connect to any other external source.

View and manage Google Drive files and folders that you have opened or created with this app: The MergeFactory will copy a document template, create a new document, and then save it to your Google Drive when a document merge, label merge, or email combo merge is processed. The MergeFactory will copy a Google Document and send it as a PDF attachment when an email merge is processed and an "Email Attachment" is included in the email template details. The MergeFactory does not delete any Drive files.

See, edit, create and delete you spreadsheets in Google Drive: The MergeFactory can edit the spreadsheet it is being used on. The add-on does not create, edit, or delete any other spreadsheets in Drive.

View and manage you Google Docs documents: The MergeFactory will copy a document template, create a new document, and then save it to your Google Drive when a document merge, label merge, or combo merge is processed. The MergeFactory will copy a Google Document and send it as a PDF attachment when an email merge is processed and an "Email Attachment" is included in the email template details. The MergeFactory does not delete any Document files.

Send email as you: The MergeFactory will send email from your primary email account when processing an email merge. When processing a premium subscription or using a coupon code, the add-on will send an email from the user's mail account to PCAdd-ons with the basics of the subscription. All emails sent by the add-on are available to view from your email sent folder.

Eliminated Permissions

Google is changing policies to ensure more trust in third party add-ons access to user information. The following permission were recently eliminated in order to comply with these changes. Full email access was downgraded to the send_mail permission on 2/27/2019 due to Gmail policy changes. Full access to Google Drive was downgraded to the Drive.files permission on 10/22/2019 due to Drive policy changes. The following permissions are no longer required by the add-on, but are the reason some functionality has changed. 

View your email messages and settings: The MergeFactory will search your current email drafts, find the draft with a subject of "MergeFactory", and then copy the html body of this specific message when the user clicks the "Import Draft" button in the "Email Merge Sidebar". No other email messages are viewed or read by the add-on.

See, edit, create, and delete all your Google Drive files: The MergeFactory will copy, create new documents, and then save them from your template files when a document or label merge is processed. The MergeFactory will copy Google Drive files and send them as attachments when an email merge is processed with "Attach Google Drive File ID's" included in the email details. The MergeFactory does not delete any Drive files.

Known Limitations

• Email Limits - The number of emails this add-on can send in a day is limited by your Google account. The current stated limit by Google is 100 emails per day for standard Google accounts and 1500 per day for the paid G Suite accounts. Email limits are not specific to this add-on, but refer to your total daily limit for all Google services. We offer a way to check your remaining email limit from within the Show email sidebar, the Go premium sidebar, and the About sidebar.
• Email Attachment Limits - Email attachments are limited to one Google Document file which will be copied and sent as a PDF attachment. Premium combo merges will also attach the selected merged document and send it as a PDF file. The sharing settings of the Document file is not relevant, since you are authorizing it to be copied and then sent out as a PDF attachment in an email.
• Emails with embedded images - The default plain text does not allow for special formatting or including images in the body of the email. This can be accomplished if you host your own images and create your own HTML email templates and past the raw HTML code into the Email Body and select the HTML email option. We offer a "MF Get My Gmail Draft" script for premium subscribers, that allows you to create your template in Gmail and utilize the basic formatting of Gmail and will also include your custom signature if you have created one in your Gmail setting. This will import the HTML from your Gmail, but will not include attachments or images inserted into the body of the email.
• Column references are static - Google Sheets columns can be moved by drag-n-drop or new columns can be inserted at any time. This should not effect the add-on, but you may need to update the column references in your Doc and Email templates to account for the new column letter references.
• Invalid emails - The add-on does not check for the validity of email addresses in your spreadsheets or entered into the email template fields. The results will vary according to the severity of the improper email address, and it will either send the email to the improper email address or the script will respond with a ScriptError: Invalid email: and the current action will end. Email fields can also include several coma separated valid email addresses.
• Doc Save As Name - When creating a Document template you can use column data marker references in the Doc Save As Name, but since the document may have several rows merged into the one document only, the first row information will be used in the Doc Save As Name. Individual output documents can easily be created by selecting a single row and repeatedly creating a document merge for each individual row. The @date@ and @time@ references can be used for the Doc Save As Name and can be handy in finding the correct documents in Google Drive.
• Document Header & Footer - Similar to the Doc Save As Name above, you can use column data markers in the header & footer of your document. However, only the first row information will be used in the headers and footers since the headers and footers are copied and identical on each subsequent page.
• Document Template Images - (1) Document templates with images should use the default option of "In line" as the positioning option. The use of the "Wrap text" or "Break text" are anchored or positioned images and do not always work as expected after the first page in the new merged document. (2) Images inserted inside of tables in document templates do not copy when creating multi-page merged documents. The current work around is to insert the image as a new Google Drawing and inserting the image into the drawing which should resolve the issue. Here is an explanation video.
• Google Script Limits - Google limits add-on scripts to a certain amount of continuous runtime. This means if you have a spreadsheet with thousands of rows or columns of data it is possible that some executions of the script may time out and not finish completely. We do not limit the amount of Docs or Emails in a single execution, but we recommend keeping the number of rows in your selections to a reasonable limit for any single action. There are practical limits to the size of an email body and the number of templates that can be created for a single Google Sheet. I have not run into these limits yet and will advise if or when these are known.
• Shared Spreadsheets - Each spreadsheet will have its own set of templates and each user can edit these templates, but they are the same templates for all users of the shared spreadsheet. Google recently (10/2019) moved to a per-file authorization to access Google Drive files. This means that each user in a shared spreadsheet has to use the Google Picker to either re-select a template another user has added or have used Google Picker to select that same document in another spreadsheet so that the add-on can access that file for each individual user. This is not multi-user friendly, but we had to make these changes in order to follow Google Drive access policies. This also means that document templates should not only be shared, but also be placed in a shared folder, so merged documents can be created in the same default folder. A different Save To Folder can be used, but this would also need to be a shared folder.

Known Issues

Document Template Images:

December 2019: Google Document templates with images that are residing inside of tables created in the document are only working in the first page of a merged document. Subsequent rows or fill in pages will not display the image, but will just show the outline of where the image should be. Images in the header and footer are not affected by this since they are created with the first page and simply repeated throughout the merged document. I am working on the issue to see if it can be resolved with the Document API's that Google provides.

Miscellaneous

The add-on only utilizes Google services and does not share any of your data outside of the Google environment or with me as the developer. The script has access to your email in order to send emails on your behalf and also access to your Google Drive in order to copy and create new documents. The only information I receive is your email address and postal code if you subscribe to our premium version, use a coupon code, or provide feedback information. I run a small business and have developed Google Scripts to help streamline some of my order processing and have decided to publish this add-on and hope that others find it useful in enhancing the Google ecosystem.