Convert a Google Document into an EPUB File

With Google Documents, you can easily save any document into an ePub file in the browser. Just open any document inside the Google Documents web editor and choose File > Download > EPUB from the menu to convert the file.

If you have bunch of documents in your Google Drive, converting them to EPUB format manually would be a tedious task and that’s where Apps Script can help.

function convertDocToEPUB() {
    // Get the currently opened document
    var document = DocumentApp.getActiveDocument();

    // Get the EPUB export link
    var mimeType = "application/epub+zip";
    var exportLink = Drive.Files.get(document.getId()).exportLinks[mimeType];

    // Get the blob of the epub exported file
    var response = UrlFetchApp.fetch(exportLink, {
        headers: {
            Authorization: "Bearer " + ScriptApp.getOAuthToken()
        }
    });

    // Save the EPUB file in the Google Drive root folder
    var file = DriveApp.createFile(response.getBlob());
    // Set the file name same as the document name
    file.setName(document.getName() + ".epub");

    // Return the file URL from Drive
    return file.getUrl();
}

We use the Google Drive Advanced Service of Apps Script to get get the export link for the EPUB MIME Type. Next, the URLFetchApp service exports the Google Doc to the requested MIME type and returns the exported content as a blob.

The blog is saved to Google Drive and the file URL is returned.

A similar technique is used by Document Studio to convert merged documents into EPUB ebooks.

Create Gmail Labels with the Gmail API

You can create labels or folders in Gmail programmatically with the Gmail API. The GmailApp service of Google Apps Script includes the GmailApp.createLabel() method to quickly create new labels but one downside is that this method doesn’t return the internal id of the Gmail Label.

The Gmail Label Id is required if you wish to apply that label to multiple Gmail threads in one go. Thus we are using the advanced Gmail service available inside Google Scripts to create Gmail Labels.

const createGmailLabel = labelName => {
  const { labels = [] } = Gmail.Users.Labels.list('me');
  for (let i = 0; i < labels.length; i += 1) {
    const { name, id } = labels[i];
    if (name === labelName) {
      return id;
    }
  }
  const { id: newLabelId } = Gmail.Users.Labels.create(
    {
      name: labelName,
      labelListVisibility: 'labelShow',
      messageListVisibility: 'show'
    },
    'me'
  );
  return newLabelId;
};

If you are to apply this Gmail label to an array of Gmail messages, use the batchModify method available inside Gmail API.

const applyGmailLabel = (messageIds, labelName) => {
  const labelId = createGmailLabel(labelName);
  Gmail.Users.Messages.batchModify(
    {
      addLabelIds: [labelId],
      ids: messageIds
    },
    'me'
  );
};

These examples are written with ES6 and Apps Script.

The Essential Tools for Programmers

This is a list of essential tools and services from my coding workflow that I think should be part of every web programmer’s toolkit. Whether you a building a simple “Hello World” app or a complex web application, these tools should make your coding easier and increase productivity.

programmers-toolkit.jpg

The Web Developer’s Toolkit

1. devdocs.io — API documentation for all popular programming languages and frameworks. Includes instant search and works offline too.

2. glitch.com — create your own web apps in the browser, import GitHub repos, use any NPM package or build on any popular frameworks and directly deploy to Firebase.

3. bundlephobia.com — quickly find the import cost (download size) of any package in the NPM registry. Or upload your package.json file to scan all dependencies in your project.

4. babeljs.io/repl — Write your code in modern JavaScript and let Babel transform your code into JavaScript that is compatible with even older browsers.

5. codeply.com — quickly build frontend responsive layouts with frameworks like Bootstrap, Materialize CSS and SemanticUI.

6. httpie.org — a command-line tool that is useful for making HTTP requests to web servers and RESTful APIs. Almost as powerful as CURL and Wget but simpler.

7. regexr.com — A good tool for testing your regular expressions in the browser.

8. jex.im/regulex — Write any regular expression into the editor and get a visual representation of how the pieces work.

9. buildregex.com — Construct regular expressions visually.

Also see: The Most Useful Websites on the Internet

10. explainshell.com — Type any Unix command and get a visual explanation of each flag and argument in the command.

11. tldr.ostera.io — Unix man pages are long and complex. This site offers practical examples for all popular Unix command without you having to dive into the man pages.

12. mockaroo.com — quickly generate dummy test data in the browser in CSV, JSON, SQL and other export formats.

13. jsdelivr.com — Serve any GitHub file or WordPress plugin through a CDN. Combine multiple files in a single URL, add “.min” to any JS/CSS file to get a minified version automatically. Also see unpkg.com.

14. carbon.now.sh — create beautiful screenshots of your source code. Offers syntax highlighting for all popular languages.

15. wakatime.com — know exactly how long you spend coding with detailed metrics per file and even language. Integrates with VS Code, Sublime text, and all popular code editors.

16. astexplorer.net — paste your JavaScript code into the editor and generate the Abstract Syntax Tree that will help you understand how the JavaScript parser works.

17. hyper.is — A better alternative to the command line terminal and also iTerm. Use with the Oh My Zsh shell and add superpowers to your terminal.

18. curlbuilder.com — make your own CURL requests in the browser.

19. htaccess.madewithlove.be — easily test the redirect and rewrite rules in the .htaccess file of your Apache server. See useful .htaccess snippets.

Also see: The Best Places to Download HTML Templates

20. trackjs.com — monitor errors in your JavaScript based web projects and get instant email notifications when a new error is detected.

21. ngrok.com — Start a local web server, fire up ngrok, point to the port where the localhost is running and get a public URL of your tunnel.

22. codeshare.io — An online code editor for pair programming, live interviews with video conferences or for teaching code to students remotely.

23. webhooks.site — Easily inspect the payloads and debug HTTP webhooks in the browser. All HTTP requests are logged in real-time. Another good alternative is RequestBin.

24. surge.sh — the easiest way to deploy web pages and other static content from the command line. Supports custom domains and SSL. Also see Zeit Now.

25. visbug — A must-have add-on for web developers that brings useful web design tools right in your browser. Available for Google Chrome and Firefox.

26. puppeteersandbox.com — Puppeteer is a Node.js framework for automating Google Chrome. Use the sandbox to quickly test your scripts in the browser. Also see try-puppeteer.com.

27. prettier.io/playground — Beautify your JavaScript and TypeScript code using Prettier, the favorite code formatter of programmers.

28. json.parser.online.fr — The only JSON parser you’ll ever need to analyze and beautify your complex JSON strings.

29. scrimba.com — Create your own programming screencasts in the browser or watch other developers code.code.

30. katacoda.com — A training platform for software developers where anyone can create their own dedicated and interactive training environments.

31. codesandbox.io — A full-featured online IDE where you can create web applications in all popular languages including vanilla JavaScript, React, TypeScript, Vue and Angular. Also see StackBlitz.com and Repl.it.

32. apify.com — Write your own web scrapers using JavaScript and schedule your scrapers to run at specific intervals automatically.

33. vim-adventures.com — The Vim text editor is hugely popular among programmers. The site will help you master the various key commands through a game.

34. insomnia.rest — A desktop based REST client that lets you create HTTP requests and view response details all in a easy-to-use interface. Advanced users may consider Postman.

Also see: The Most Awesome Teachers for Learning Web Development

How to Rename Gmail Emails and File Attachments in Google Drive

The premium version of Save Emails offers an easy option to rename file attachments and email messages when the are downloaded to your Google Drive for archiving.

For instance, if the attachment name is “resume.pdf”, you can write a rule that will rename that attachment to, say, “Jan-20-2017 Angus McDonald resume.pdf” where Jan-20 is the date of the email message and Angus is the name of the sender.

Open your Google Spreadsheet, go to Addons > Save Emails > Create Rule (or modify an existing rule) and go to the download section as shown below:

rename-file-attachments.png

Similar to Mail Merge, you use variable name enclosed in double curly braces.

For instance, if you need to include the name of the sender and the message’s subject in the saved email, you set the File name as {{Subject}} {{Sender}}. Email messages are downloaded in PDF format and the .pdf extension will be added to the name automatically.

For attachments, you can use {{AttachmentName}} (no spaces) to have the saved file name exactly as the original file.

Or use the following format to set the name depending on the date of the message.

{{Year}}-{{Month}} {{Sender}}.{{AttachmentType}}

Here’s a complete list of variables that you can use while setting the name of emails and file attachments with save emails.

VariableDescription / Example
{{AttachmentName}}Original file name (e.g. resume.pdf)
{{AttachmentBase}}Base name of the file (e.g., resume)
{{AttachmentType}}File extension or format (e.g., pdf)
{{MessageId}}Email Message Id
{{ThreadId}}Email Thread Id
{{Date}} (format can be customized)Message Date (e.g., 26-Feb-2017)
{{Day}}26 (dd)
{{DayName}}Wed (EEE)
{{DayNameFull}}Wednesday (EEEEE)
{{Week}}Week Number (ww)
{{Month}}Feb (MMM)
{{MonthNumber}}02 (MM)
{{MonthName}}February (MMMMM)
{{Year}}2017 (YYYY)
{{Sender}}Sender’s Name (from the From field)
{{Recipient}}Recipients Name (from the To field)
{{Subject}}Email Subject

Compare Free and Premium Editions of Form Notifications

The Google Forms notification add-on is available as a free download for all Gmail and G Suite accounts.  The basic features are free but you can upgrade to premium for more features.

The license applies to the entire Google account and thus all Google Forms under the same Google Account will be upgraded to premium. In other words, you require a single license for all Google Forms in your account. Also, the license is only required by the form owner and not the form respondents.

FeatureFree EditionPremium Edition
Email LimitMax 20 responses per daySend 400 - 1500 email recipients per day depending on the type of your Gmail / GSuite account. Learn more.
Send multiple emails per Google Form submissionYesYes
Number of Form Rules allowedOneUnlimited
CC and BCCNot availableAdd one or more email recipients in the CC or BCC field of the email
Notify form respondentYesYes
Send email notifications from a different email aliasYesYes
“Sent via” branding removed from email notifications.NoYes, all branding removed from emails
Send emails from a generic no-reply email addressNoYes (requires a G Suite or Google Apps account)
Send conditional email notifications based on form answersNoYes, send responses to different people based on user’s answers
Manually resend email notifications to old responsesNoYes
Technical support includedNoEmail support included
Premium Upgrade-
Upgrade to Premium

GDPR Compliance

The General Data Protection Regulation (GDPR) is aimed at giving citizens throughout the European Union (EU) control over their personal data. GDPR applies to you if you are a European Union citizen or, as a business, if your email subscribers are EU citizens, or you deal with any kind of personal data of EU citizens.

How our add-ons enable you to comply with EU’s GDPR policies

As a customer, you operate as the data controller and we are considered a data processor. You have the responsibility for ensuring that the personal data of subjects you are collecting is being processed lawfully and, similar to controllers, processors, that processes personal data on behalf of a data controller, are expected to comply with the GDPR.

Data Collection

With GDPR, you must have explicit consent from your email subscribers that they would like to receive emails from you. It is recommended that you use double opt-in to align with GDPR compliance requirements. For EU individuals who are already on your marketing lists, you may need to contact them by email asking them to confirm their consent.

You should include a visible unsubscribe link in your marketing emails that your subscribers can click to instantly unsubscribe from all your future communications.

Data Storage and Processing

All your customer’s data is stored in your Google account, inside Google Sheets, Docs, Gmail, Google Drive or Google Forms and not on our servers. Our addons read the data directly from your data source and perform the necessary actions (like sending emails, generating documents, saving emails) without transferring any personal data.

The email messages are not stored on our servers. If you choose to attach Google Drive files in your emails, the content of the files are not stored on our servers. Your form submissions are not stored on our servers. If you enable optional campaign tracking (opens, clicks, unsubscribes), the email addresses of recipients, unsubscribers, and bounced emails are stored in the database for reporting.

We store and process user data in Google Cloud database (us-east) and its servers are located in the United States (East) data center.

We use Google’ Stackdriver logging tool for error tracking and debugging errors. It includes stack traces, error messages and the logs do not include any PII data.

We use PayPal, Stripe, and Paddle to manage your payments. The payment processors only provide the customer’s email address and, in case of PayPal, the shipping address for generating invoice. We do not have access to any banking or credit card information of our customers.

Data Portability

We do not transfer, sell, make copies, or share any of your data processed by our Google Add-ons to third party services or companies. We only store data that is absolutely necessary for our add-ons to function.

You can use download and export all your subscriber’s information in Google Sheets. This allows for easier migration to other services.

Data Erasure (Right to be forgotten)

All addons have a deactivate option that will permanently delete all user’s data from the database. You can also contact us to submit a deletion requires and, in compliance towards GDPR, we’ll permanently delete all your data.

If you uninstall a Google Addon, or revoke access to the addon from your Google Account, the add-on will not be able to access any of your data and will instantly stop functioning.

Our Google Addons use your own Gmail account to send emails. You can feed your customer profile data directly into our Google Add-ons - through Google Sheets and Google Forms - to send marketing emails, transaction emails and form notifications. Our tools only facilitate your compliance to GDPR, your sending practice is key to complying with GDPR.

Contact Us

If you have any questions, please contact us by email: amit@labnol.org

How to Use Google Sheets as an Amazon Price Tracker

The prices of products listed on various Amazon shopping websites may vary every day and a simple Google Spreadsheet can help you monitor these price fluctuations via email. Just add the Amazon items in a spreadsheet and you’ll automatically get email alerts when their prices change on Amazon. You’ll thus never miss the deal again.

amazon price tracker

Track Amazon Prices with Google Sheets

The Amazon Price Tracker is easy to configure. All you have to do is add the links (URLs) of Amazon product pages inside a Google Spreadsheet. You can add monitor products across all Amazon.* websites. Here are the steps involved:

Step 1: Generate Amazon Access Keys

Open the Amazon Associates website and sign-in with your Amazon account. Next, click the Tools menu, choose Product Advertising API from the dropdown (link) and choose Manage Your Credentials under the Add Credentials section. Amazon will create an Access Key ID and a Secret Key that you’ll need in the next step.

Step 2: Copy the Google Spreadsheet

Click here to copy the Price Tracker Google Sheet in your Google Account. DO NOT change the layout of the Google Sheet as it may break the tracker.

amazon-product-links.png

Now all you have to do is enter Amazon product URLs in column A of the Google Sheet, one per line. For instance, here’s the Amazon URL for iPad

https://www.amazon.com/dp/B07K344J3N

The URL contains the 10 digit ASIN code which is used by the Google Sheet to identify the specific Amazon product you are looking to monitor for price fluctuations.

In addition to prices, the Google Sheet lists several other useful attributes of Amazon Products including merchant’s name, sales rank, overall savings, Prime eligibility and whether the product qualifies for free shipping.

Step 3: Set Price Thresholds (optional)

With the Price Tracker, you can also set price thresholds for specific products and get automatic email alerts when the target price is reached. For instance, you can choose to receive alerts when the iPad price drops by 20% of the initial value. Or you can set a target price (alert when iPad drops to $399) and get automatic alerts when the price drops on Amazon.

To specify a threshold for any product, go to column B of the product row and enter the amount or percentage values as shown in the screenshot above.

Step 4: Configure Price Tracker inside Google Sheet

Inside the Google Sheets, go to the Amazon Price Tracker menu (near the Help menu) and choose Configure. You may have to authorize the Pricer Tracker script once to connect to Amazon API and send email alerts.

configure-price-tracker.png

Next enter your access keys, your email address and the time when you wish to receive the email alerts. Click Save to initialize the Amazon Price Tracker.

That’s it. You’ll receive a daily email digest with the price fluctuations as shown below.

amazon-email.png

Monitor Amazon Products for Price Drops

Amazon Price Tracker can monitor prices of all products (including Kindle ebooks) listed on Amazon.com and the country-specific websites including amazon.fr, amazon.de, amazon.es, amazon.ca, amazon.it, amazon.in, amazon.co.jp and amazon.co.uk.

Also, you can add more Amazon URLs, or delete existing products, from the Google sheet and the script will automatically pick the changes in the next run. And if you ever wish to stop receiving those email alerts, just choose the Stop Tracking option from the Price Tracker menu in the same Google sheet.

The tracker is written in Google Scripts and uses Amazon’s Product Advertising API to fetch the latest prices at fixed intervals. The Price Tracker is completely free but if you chose to upgrade to premium, you get technical support and get to use your own Amazon Associate tags.

What is the Daily Email Sending Limit for Gmail Mail Merge

With Mail Merge for Gmail, you can send emails to anywhere between 300 to 1500 email recipients per day and your daily sending limit will depend on the type of your Google account. 

How to Check your Email Sending Limit

You can use the Mail Merge program to quickly check your email sending limit in Gmail. Here’s how:

  1. Install the Mail Merge program.
  2. Inside your Google Sheets, go to Addons menu, choose Mail Merge with Attachments and then click the Show Email Quota menu.
  3. It will show your current email sending quota.

Email Sending Limit - Gmail Mail Merge

Mail Merge Quota for Gmail Accounts

If you have a @gmail account, you can email up to 50 email recipients per 24 hours with the free edition of Mail Merge. Premium users can email up to 300 email recipients per day with mail merge for Gmail.

Mail Merge Quota for GSuite (Google Apps)

There are two categories of GSuite accounts.

  1. Legacy Google Apps accounts, or GSuite Legacy, that are completely free.
  2. GSuite accounts where you pay Google a monthly fee per user.

Limit for GSuite Legacy

If you have a free Google Apps legacy account, where you don’t pay Google a monthly subscription fee per user, your daily sending limit for Mail merge will be the same as those of free Gmail accounts.

Limit for GSuite for Work

If you have a paid GSuite account (GSuite Basic, GSuite Business, or GSuite Enterprise), you can send up to 1500 emails per day with the premium version of Mail merge.  GSuite for Education, Government, and Nonprofits also have the higher 1500 limit provided you have upgraded to the premium edition of Mail Merge.

Free (download)Premium (buy)
Gmail (@gmail)50300
GSuite Legacy (free)50300
GSuite (paid)501500

The Mail Merge limits are per user of the GSuite domain. So if your organization has, say 5 users, each user in the domain will have their own limit and it is not shared with other users.

Email Sending Limits - Frequently Asked Questions

Check your Google Account Type

If your email address ends with @gmail.com or @googlemail.com, you have a free Gmail account, else it is a GSuite account.

To check the type of your GSuite account, go to this page, sign-in with your GSuite account and it will show the type of your GSuite account.

Alternatively, go to admin.google.com, sign-in with your GSuite admin account, click the Billing Section and it will show the type of your GSuite account.

Email Quota Exhausted Too Soon

Q: My mail merge email quota is 1500 email recipients per day. I’ve sent only 750 emails today and my quota is exhausted. Why?

Your daily sending quota for Gmail Mail Merge is total email recipients per day.

For every email message that you send with Mail Merge, the total number of recipients (including each recipient in the To:, CC: and BCC: fields) is counted against the maximum number of emails you can send in a 24-hour period (your sending quota).

Thus if you send 1 email but include 2 addresses in the CC or BCC fields, Gmail will count them as 3 recipients and your email quota will be reduced by 3 for that day.

Google will reset your daily quota around 1 PM PST.

Lower Email Sending Limit for GSuite Users

Q: I’ve a paid GSuite account but the email sending limit with Mail Merge is still limited to 300 email recipients per day. Why is the email limit not increased even with GSuite?

This is likely because you have recently upgraded to the paid edition of GSuite. When you create a new GSuite account, or migrate from Gmail/Google Apps to GSuite Business, Google will only increase your daily email sending limit after a few billing cycles.

They initially put you in a trial mode and the sending limit is increased only after your account is removed from trial. To move your account out of trial earlier, you can either make an early payment or increase the number of users in your GSuite domain.

Your limits are automatically increased when your GSuite account is cumulatively billed for at least USD $30 (or equivalent). If you purchased your domain from Google, the amount required to increase your sending limits is USD $30 plus the cost of your domain.

Please contact GSuite Support directly for help on how to move your account out of trial mode. Google Support says that any newly created Google Apps domains are subject to the consumer limit for the first billing cycle if they have six or more users, or several billing cycles if they have fewer uses.

Mail Merge Limit vs Gmail Limit

Q: Why can I send 1500 emails per day with Mail Merge when I can send more number of emails on Gmail website?

The sending limit for Gmail is different from Mail Merge because the latter sends emails through Gmail APIs that have a different email sending quota.

Find Out How Much Traffic a Website Gets

Would you like to know how much traffic (or page views) other websites in your niche are getting for competitive analysis? While it will be difficult for any third-party to accurately measure the traffic of a site, there are a bunch of traffic estimation services that can give you a better understanding of the popularity of a website. Here’s a list:

1. Similar Web - It started as a tool for finding similar sites but now offers a range of data including traffic analysis in a clean interface. You get to know the site’s traffic over time, the countries that are sending the most traffic, what search keywords are bringing the organic referrals, how much time users are spending on a site and so on. The traffic reports can be downloaded as PDF files for archiving.

2. SEM Rush - It offers plenty of information around search (organic) traffic for any website. Put in the website URL and you’ll instantly know how the site has fared in organic search over time. The data can be split by country, you get to know which sites are sending traffic and also the keywords that are bringing the most visitors. Open Site Explorer is another popular tool in this category.

3. Alexa - Enter the website’s domain and Alexa will reveal the ranking of that website based on a combined measure of unique visitors and page views. The rank isn’t based on a site’s traffic alone but is relative to the traffic of all other sites that are monitored by Alexa. Webmasters are often skeptical of Alexa number but the rank is still a good indicator of a website’s popularity over time.

4. QuantCast - Like Compete, you can use QuantCast to determine how many people have visited a selected site during a given period from desktop or mobile phone. For sites that have implemented the QuantCast tags, you get more accurate metrics including visitor demographics, traffic by country, and the split in mobile and desktop traffic.

  1. Ahref - With Ahrefs SEO toolkit, you can see how much organic search traffic your competitor’s website is generating and what are the top search keywords that the website ranks for in Google search.

Also see: Know Everything About a Web Site

Website Traffic Chart

Find Website Traffic with Google AdWords

Sometime back, Google Ad Planner and Google Trends for Websites were the go-to tools for estimating a site’s traffic but they have since been discontinued. You can however still use Google AdWords to get a good idea of a site’s traffic and demographics. Here’s how:

Sign-in to Adwords with your Google Account. You need not be an AdWords advertiser to use this tool.

Next put the site’s domain in the search box, set the Campaign targeting to United States (or remove geographic targeting to see global traffic) and click “Get Placement Ideas.” Google will now show you the average impressions per week for that domain and if you multiply that number by 4, you get a good estimate of the site’s monthly traffic (hits).

How to Use Template {{Markers}} in Document Studio

Your Document Studio templates can include any variable field that is available as a column title in the Google Sheet. In case of Google Forms, you can use any question title as your marker and the marker should be exactly as your question title enclosed in double curly braces.

For instance, if a question in your Google Form says “What is your name”, the marker you’ll use in your document template will be {{What is your name}}.

You can also create additional columns in the source Google Spreadsheet with formulas and use them as dynamic markers in your Google template. Let me show you an example.

Create a new column and call it Row Number. Now go to the first empty cell of that column and put this ArrayFormula function:

=ArrayFormula(if(LEN(A2:A); row(A2:A) -1;))

What we have added a column that displays the row number of the rows and we can now put {{Row Number}} in your document templates, merged file names and Drive folder path. For instance, if you put Invoice #{{Row Number}} in the file name, the files name would be named Invoice #1, Invoice #2 and so on automatically.

Formatting Date and Time

The same technique can be used to format date and time information for including in your templates.

Say the first column in your Google Sheet in called Timestamp and it includes the date and time when a Google Form response was submitted. Add a new column called Submission Time and add this formula in the first empty cell.

=ArrayFormula(if(NOT(ISBLANK(A2:A)),HOUR(A2:A)&":"&MINUTE(A2:A),))

You can now include the submission time in your email and document template using the {{Submission Time}} variable field.

Standard Markers for Google Forms

In addition to standard form questions and column headers, Google Forms also support a few standard markers like:

  • {{Response Number}} - Form Response number
  • {{Form Name}} - The name of the Google Form
  • {{Form Url}} - A link to the associated Google Form
  • {{Response Id}} - Unique response ID generated by Google Form
  • {{Response Url}} - Directly link to view / edit the current form submission
  • {{Response Date}} - The full timestamp when a form was submitted
  • {{Respondent Email}} - If your Google Form is capturing email addresses, this will include the email address of the form respondent.
  • {{All Markers}} - An HTML table containing all the form responses in a neat tabular format.

You should put {{All Answers}} in your email body to receive a copy of the form answers in an email notification.