It would take the least amount of effort to send the URL of the online spreadsheet to everyone involved. But a summary appearing every day in the inbox is more likely to give stakeholders regular insight into the latest data.
There are many services for sending newsletters with no or little code. On the other hand, you still need a tool to automate report generation and scheduling it. You can use ready-made solutions such as Tableau, Qlikview, Google Data Studio or self-hosted alternatives, but those require additional costs and time, especially to integrate them into email delivery service. You also need an always-on automation server and you are unlikely to want to play with the administration of a large system just to send few messages.
When you add to that the need to support multiple language versions, the level of complexity can arise beyond the scope of what was supposed to be a relatively simple project. Fortunately, only two tools are needed to achieve the objectives: Google Sheets and Localazy.
🤔 Why?
Imagine that you work in the analytics team at an international company that has 3 branches in different countries (England, Germany, Poland).
Employees in each branch need to receive a summary of sales for the day.
Sales data is continuously updated in Google Sheets.
So let’s generate and schedule the newsletter with a report using it.
📧 Sending email from Google Sheets
The first sheet named data contains sales data for each location and day. Data could be fetched at some intervals from an ERP or accounting system. emails sheet contains an email list with some setting for each addressee. Let’s get on with sending messages first.
To create a new Apps Script project, click on Tools > Script editor. Paste the code below to the Code.gs file.
https://gist.github.com/fischerbach/2db069c082651418ebf76ed48b6308ac
Next, you should select the sendEmail function from the dropdown menu and click the play button.
After that, you will probably be asked for permission to access your data. An email should appear in your inbox, sent from your Gmail address:
📮 Sending newsletter from Google Sheets
One of the biggest advantages of Apps Script is the ease with which you can transfer data between different services in the Google ecosystem. Most Google applications have a dedicated Apps Script service, for example, Gmail has the Gmail service and Google Sheets has the Spreadsheet service. With these built-in services, you can quickly extract an email list from a spreadsheet, generate a report and send the result using Gmail.
Update Code.gs with the following code:
Each call to the sendEmails function will retrieve email addresses and check the date of the last newsletter. If it is older than today, the report will be sent. This safeguards against sending too many messages.
📊 Generating daily report
Now the flexibility of our solution becomes apparent. We want to prepare a report containing data for a specific branch on a specific date. It would be moderately easy to prepare something like this in Python, depending on the data source and what the output report should look like. Even more so in an Excel-like environment, where it needs practically a few clicks.
In Google Sheets, we make a few charts and summaries using pivot tables.
For more details refer to the example file (sheet report).
💬 Customizing messages
As we have employees from different branches, each of them receives a different report. All elements of the report are saved and updated in the spreadsheet. Now it is enough to glue them together. This is done by the generateReport function:
Full code available here
🌐 Multiple language versions
Your company employs people from different countries and with varying levels of English proficiency. As some terms may be difficult for them to understand, let’s prepare other language versions of the report. Your report is generated automatically once a day, so having it translated manually into national languages every time is not an option.
In theory, automating another language version does not seem complicated. After all, it is enough to translate individual labels and descriptions into another language and then hardcode them in the next version of the script.
Although with a set-and-forget type of things this simple approach could even work, in practice, is time-consuming and error-prone. Especially when there is a recurring need to modify some messages. And you will quickly find out modifications are always needed.
Not to mention the difficulty of working with non-technical translators using this method. But if you can automate report generation, why not do the same with translation?
Pipeline is simple. Language versions are stored in a single file, translated and downloaded from Localazy. This file is stored in Google Drive and loaded into Sheets and Apps Script. Apps Script generates a report and sends it by Gmail. (Pic. by author. All logos belong to their respective owners and are used for informational or editorial purposes to identify specific products and services.)
🚩 Localization
First, let’s prepare a JSON file with the labels:
Upload it to your Google Drive and find its id (in sharing url, just like in Sheets). Then, let’s add a new function in Code.gs that takes any string and the target language. If a suitable translation is found in the lang.json file, the function will return it. If otherwise, it will simply return the input string.
Note: the concept presented here is a simplified version of the approach using [GNU Gettext](/dictionary/gnu-gettext, e.g. for localizing Python scripts.
Now let’s simply surround the _t function with all labels that require localization. This is the final version of the Code.gs file:
🚀 Localazy
Now we need to find a way to manage the language versions stored in the lang.json file. Localazy provides a convenient GUI for this purpose.
Localazy is an awesome piece of software that makes the usually awful translation experience bearable and even almost enjoyable. Because it supports JSON files, the possibilities for integration into any workflow are virtually endless. First, create a Localazy account and install Localazy CLI. Then, create a new application.
Then, select JSON files from available file formats. You will see a template configuration file. In it, we set up support for our labels and different languages in one file:
Save it to the same folder as the lang.json file. Go to your app on Localazy and add some new languages.
Now you can load lang.json into Localazy:
After a while, you will see a list of phrases to translate in each language of your application.
And the cherry on the top, a machine translation comes with each phrase.
Once all the translations have been accepted or created, you can download them into your application:
If you run this in a folder that syncs with Drive, you can now generate a report of the new language version. Let’s check the report in Polish:
Nice. Finally, let’s address one more issue.
📅 Report schedule
Back to Apps Script. We can now set a schedule for sending the report. In the project, select Triggers and then Add Trigger.
We set the report to be sent daily, but there are other triggers available, such as calendar events.
🤓 Takeaways
From now on, the report will be sent between 8 am and 9 am. Unlike CRON, you cannot set an exact minute. This shouldn’t be a problem for our project, but it’s worth bearing in mind if the data is fetched to the spreadsheet at specific times of the day.
As you can see, with some initial effort you can create a pretty robust serverless workflow to distribute multi-language reports. Another advantage is the use of tools available practically free of charge.
Thank you for reading. I hope you enjoyed reading as much as I enjoyed writing this for you.
If you would like to share feedback or simply say hello, you can connect with me on LinkedIn
If you enjoyed reading this, you’ll probably enjoy my other articles too: https://fischerbach.medium.com
📚 References
Apps Script: https://developers.google.com/apps-script/
Localazy: https://localazy.com/
Other:
https://developers.google.com/apps-script/articles/sending_emails
This post was originally published on Netlabe.com by Rafał Rybnik
This is a companion discussion topic for the original entry at https://localazy.com/blog/create-multi-language-newsletters-free-using-google-sheets-localazy