How to Send Google Forms with Pre-filled Answers

Prefilled Google Forms, where some of the fields in the form are pre-populated with answers you already have, make the process of filling out your forms easier and faster.

  1. Your contacts are more likely to fill out the form as it takes less time for them to complete the remaining fields.
  2. The form respondents are less likely to type incorrect data in fields, like the employee ID, that are pre-populated.
  3. The forms feels more personal when people see their name and other personalized information pre-filled in the form.

Create Pre-filled Google Forms with Google Sheets

This step-by-step video tutorial explains how you can create pre-filled Google Forms with dynamic information from a Google Sheet. You can then use Mail Merge or Document Studio to automatically send the prefilled forms to your contacts in bulk with Gmail.

In our example, the organization maintains their employee database in a Google Spreadsheet and they want to give employees an option to self-update their details in the spreadsheet with the help of Google Forms.

Google Spreadsheet with Answers

If you look at employee records in the Google Sheet carefully, you’ll find that only some details of the employees are missing in the sheet. This is a perfect use case for using prefilled Google Forms as it be wasting employee productivity if we send them a blank Google Form and require them to fill out every single field.

For instance, in row #2, we know the location and gender of Angus but his date of birth is unavailable in our records. For row #4, the employee ID and email is known but Kiran’s other details are missing.

Create the Google Form

To build this workflow, we’ll create a Google Form with fields corresponding to the columns in the source Google Sheet. Here’s how the final form would look like:

123826

Inside the Google Form editor, click the 3-dot menu choose the Get pre-filled link option. Here, fill in every field with dummy data that is easy to recognize and replace later. Once the fields have been filled, click the Get Link button to generate the prefilled link and copy it to your clipboard.

Prefilled Google Form

The link to the prefilled Google Form would look something like this.

https://docs.google.com/forms/d/e/xxxx/viewform
   ?entry.1808207196=EMPLOYEEID&entry.1663131167=EMPLOYEENAME
   &entry.1819275928=2020-06-03&entry.2071782719=Female
   &entry.175059757=Hyderabad

It’s long and complex but if you take a closer look, this is simply a collection of name and value pairs appended to the Google Form URL. Google Forms will assign a unique id to each field in the form and these are appended to the Form URL with your pre-populated value.

For instance, the Name field in your Google Form is internally represented as entry.1663131167 in the form URL. If we replace the parameter value EMPLOYEENAME in the URL with another value, that would be prepopulated in the Google Form.

And this is exactly what we’ll do to create personalized prefilled links for all the rows in our Google Sheet.

prefilled google forms

Add Form Formulas in Google Sheet

Inside your Google Spreadsheet, create a new sheet and rename it Form Link. Paste the prefilled Google Form link in the first cell (A1) of this blank sheet.

Next return to the Google Sheet that has the employee database and create a new column, say Google Form Link.

Now we need to replace the dummy values in our prefilled link with the actual values from the rows in the sheet and this can be easily done with SUBSTITUTE function of Google Sheets.

For instance, we need replace EMPLOYEENAME in the prefilled link with real names that are in column B of the spreadsheet. Our formula would be something like this:

 =SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", B2)

We’ll feed the result of this formula into another SUBSTITUTE function to replace another field, say EMPLOYEEID.

=SUBSTITUTE(
   SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", B2),
   "EMPLOYEEID", A2)

This has to be repeated for every prefilled field in the Google Form.

If your prefilled data contains space, you need to wrap the results into another SUBSTITUTE function that will replace all occurrences of spaces with the plus symbol.

Our final prefilled link would be:

=SUBSTITUTE(
  SUBSTITUTE(
   SUBSTITUTE(
    SUBSTITUTE(
     SUBSTITUTE(
      SUBSTITUTE('Form Link'!$A$1, "EMPLOYEEID", A2),
     "EMPLOYEENAME", B2),
    "2020-05-31",E2),
   "Female", C2),
  "Hyderabad", D2),
 " ", "+")

You can test the workflow using this prefilled Google Form that will write your form submission in a new row of this Google Sheet.

Copy-down the Google Forms Formula

You may use ArrayFormula to copy down formulas or, if you have only a few rows, select the first cell and drag the crosshair to the last row in the formula column as shown below:

Copydown Forumla

Handling Dates in Google Forms

If you plan to prefill dates in the Google Form, you need rewrite your dates in the Google Sheets in a format that Google Forms can recognize.

This is easy to implement. Just select the column in your Google sheet that contains the dates, then go to the Format menu, choose Number > More Formats > More date and time format and choose the YY-MM-DD format.

Also see: Create PDF from Google Forms

You can use Mail Merge with Gmail to send the prefilled forms to all the email addresses in one go from the Google Sheet itself.

When composing the email template for merge, select any text in the email body and convert it into a hyperlink. You can put the title of the column - {{Google Form Link}} as the hyperlink and this would be replaced with your Google Form link.

Google Form link

Please watch the Mail Merge tutorial to learn more.

How to Factory Reset your Gmail Account

Your phone, the iPad, and your laptop offer a ‘hard reset’ option that resets your device to the default factory settings. Once you perform a factory reset, it erases all the apps, files, and settings and there’s no way to recover the wiped off data.

Nuke Gmail Account - Remove Everything

What is Gmail Factory Reset

If you ever need to “factory reset” an old Gmail account that you no longer use, and start afresh with a clean slate, Google Scripts can help. The script will perform a series of tasks to completely reset your Gmail account:

  1. Delete all Gmail labels
  2. Delete all Gmail filters
  3. Delete all Draft messages
  4. Delete all email messages in Gmail
  5. Delete all spam messages
  6. Permanently empty your Gmail trash folder
  7. Remove Out-of-Office message
  8. Disables POP and IMAP
  9. Remove all email signatures in Gmail
  10. Stops all email forwarding

⚠️ Warning: Danger Ahead

Before you proceed, please understand that hard reset is an irreversible process and you will not be able to recover your Gmail data after the reset is complete.

The Google Script is available on Github or you can click here to make a copy of the script in your Google account. The script uses the official Gmail API to format your Gmail account.

Remove all Gmail Labels

const deleteGmailLabels = () => {
  GmailApp.getUserLabels().forEach((label) => {
    label.deleteLabel();
  });
};

Remove all Gmail Filters

const deleteGmailFilters = () => {
  const { filter: gmailFilters } = Gmail.Users.Settings.Filters.list('me');
  gmailFilters.forEach(({ id }) => {
    Gmail.Users.Settings.Filters.remove('me', id);
  });
};

Remove all Gmail Drafts

const deleteGmailDrafts = () => {
  GmailApp.getDrafts().forEach((draft) => {
    draft.deleteDraft();
  });
};

Reset Gmail Settings

Turn off vacation autoresponders, disables IMAP and POP access, removes all email signatures and disables email forwarding.

const resetGmailSettings = () => {
  const { Settings } = Gmail.Users;
  // Disable Out-of-office
  Settings.updateVacation({ enableAutoReply: false }, 'me');

  // Delete Gmail Signatures
  const { sendAs } = Settings.SendAs.list('me');
  sendAs.forEach(({ sendAsEmail }) => {
    Settings.SendAs.update({ signature: '' }, 'me', sendAsEmail);
  });

  // Disable IMAP
  Settings.updateImap({ enabled: false }, 'me');

  // Disable POP
  Settings.updatePop({ accessWindow: 'disabled' }, 'me');

  // Disable Auto Forwarding
  const { forwardingAddresses } = Settings.ForwardingAddresses.list('me');
  forwardingAddresses.forEach(({ forwardingEmail }) => {
    Settings.ForwardingAddresses.remove('me', forwardingEmail);
  });
};

Delete all Gmail Messages

This will move all inbox messages, archived message, and spam to the trash folder. Google Scripts can execute for 5 minutes in one batch so we added a check to stop the script if it is taking longer to complete.

const startTime = Date.now();
const isTimeLeft = () => {
  const ONE_SECOND = 1000;
  const MAX_EXECUTION_TIME = ONE_SECOND * 60 * 5;
  return MAX_EXECUTION_TIME > Date.now() - startTime;
};

/**
 * Move all Gmail threads to trash folder
 */
const deleteGmailThreads = () => {
  let threads = [];
  do {
    threads = GmailApp.search('in:all', 0, 100);
    if (threads.length > 0) {
      GmailApp.moveThreadsToTrash(threads);
      Utilities.sleep(1000);
    }
  } while (threads.length && isTimeLeft());
};

/**
 * Move all Spam email messages to the Gmail Recyle bin
 */
const deleteSpamEmails = () => {
  let threads = [];
  do {
    threads = GmailApp.getSpamThreads(0, 10);
    if (threads.length > 0) {
      GmailApp.moveThreadsToTrash(threads);
      Utilities.sleep(1000);
    }
  } while (threads.length && isTimeLeft());
};

Permanently Empty the Trash Folder

/**
 * Permanetly empty the Trash folder
 */
const emptyGmailTrash = () => {
  let threads = [];
  do {
    threads = GmailApp.getTrashThreads(0, 100);
    threads.forEach((thread) => {
      Gmail.Users.Threads.remove('me', thread.getId());
    });
  } while (threads.length && isTimeLeft());
};

Also see: Archive Old Emails in Gmail

How to Make Pixel Paintings with Google Spreadsheets

You have been using Google Sheets for budgeting and business but there’s another interesting use of spreadsheets - you can use them to create impressive pixel paintings in minutes.

Marina and Mallory created a bright and beautiful wall mural using Google Sheets. Japanese artist Tatsuo Horiuchi uses Microsoft Excel to draw masterpieces.

The idea is simple. Each cell in the spreadsheet corresponds to a pixel in the painting. You find the color of the pixel and set that as the background color of the corresponding cell in the sheet. Now resize the cells in small perfect squares and your spreadsheet will resemble the original artwork.

How to Paint with Google Spreadsheets

If you would like to create your own spreadsheet art but don’t have the time to carefully paint every cell manually, here’s a simple workaround for you. You can take any photograph, vector art, GIF, or any other image and use Google Sheets to convert that bitmap image into spreadsheet art.

Here’s some artwork created with Google Spreadsheets.

Pixel Painting

Emoji Painting

Emoji Art

Watch the video tutorial or open this Google Sheet to explore more artwork made with Google Sheets. You can increase the zoom to view individual pixels.

Create Pixel Art with Google Sheets

It takes three easy steps to make pixel art with Google Sheets.

  1. Install Pixel Art for Google Sheets.
  2. Type sheets.new in the browser to create a new Google Sheet. Go to Add-ons menu, choose Pixel Art and then select Open.
  3. Upload any image from your desktop, hit the Draw button and watch as your sheet magically transforms into beautiful art.

Check this Google Sheet for more examples.

How Pixel Art Works?

The underlying Google Script will now parse every single pixel of your image and write the corresponding hex color codes in the cells of the spreadsheet.

It will then set the background color of every cell as the color of the corresponding pixel in the uploaded image. If you have chosen the ‘emoji’ option, the cell will be filled with the closest emoji that matches the color of the pixel.

In the last step, the Google Script will resize the cells and turn them into perfect squares. That’s it. Your spreadsheet art is now ready.

Download Pixel Art

If you choose to transform your picture into emojis pixels, the script takes the average color of the pixel, finds the closest emoji and adds it to the corresponding cell in the sheet. The emoji module is based on the work of Monica Dinculescu.

How to Handle GET and POST HTTP Requests in Google Apps Script

With Google Apps Script, you can easily create a Web App that serves HTML, JSON, XML or plain text output using the HTML service. When your publish your Google Script project as a web app, the script gets a public URL (think API) that can be invoked from external applications using either HTTP GET or POST requests with query parameters and request body.

When publishing the script as a web app, make sure to choose “Allow anonymous access” and execute the script as yourself. If you edit the script, create a new version inside the script editor and deploy the latest version.

Here are some examples that demonstrate how you can convert your Google Script into a web API by adding the doGet and doPost methods to your project.

Handling GET Requests

When a script is published as a web app, the doGet callback function handles all GET requests made to the script’s public URL. The Google Script can return plain text content, HTML or JSON data as shown in the examples below:

Return Text Content

const doGet = (event = {}) => {
  const { parameter } = event;
  const { name = 'Anonymous', country = 'Unknown' } = parameter;
  const output = `Hello ${name} from ${country}`;
  return ContentService.createTextOutput(output);
};

Any query parameters added to the Google Script URL, like name and country in our example, become available in the parameter property of the event object of the doGet and doPost methods in Apps Script.

https://script.google.com/macros/s/12345/exec?name=Amit&country=India

If something is not working, you can always log the request object to the StackDrive console logs and easily debug the full request.

console.log(`doGet`, JSON.stringify(event));

Serve JSON Output

The same ContentService can be used to return JSON output by using the setMimeType method with the mime set as ContentService.MimeType.JSON.

const doGet = (event = {}) => {
  const { parameter } = event;
  const { name = 'Anonymous', country = 'Unknown' } = parameter;
  const message = `Hello ${name} from ${country}`;
  const json = { name, country, message };
  return ContentService.createTextOutput(JSON.stringify(json)).setMimeType(
    ContentService.MimeType.JSON
  );
};

When testing HTTP requests in Google Script with utilities like CURL or Postman, ensure that that “Automatically follow redirects Follow HTTP 3xx responses as redirects” setting is turned on since the ContentService serves a 301 redirect from the script.googleusercontent.com domain.

Serving HTML Content

Your Google Apps script project can serve HTML web pages with the HtmlService service. The web pages served with App Script included Google warning header at the top but it can be removed if you embed the Google Script in another web page (like Google Sites) with the IFRAME tag.

const doGet = (event = {}) => {
  const { parameter } = event;
  const { name = 'Anonymous', color = 'Black' } = parameter;
  const html = `<p><b>${name}'s</b> favorite color is <font color="${color}">${color}</font></p>`;
  return HtmlService.createHtmlOutput(html)
    .setTitle('Apps Script Webpage')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
};

You should set the X-Frame-Options header of the webpage to XFrameOptionsMode.ALLOWALL to allow other pages to embed your Google Script HTML page.

Handle POST Requests with Google Scripts

The callback function doPost is invoked when an HTTP POST request is make to your Google Script URL that is published as a web app with anonymous access.

const doPost = (request) => {
  console.log(request);
  return ContentService.crateTextOutput(JSON.stringify(request));
};

The request argument of the doPost method can include:

  1. queryString - The name-value pairs sent in the URL of the request (name=Mike&age=12)

  2. parameter - The query string name-value pairs are also accessible inside the parameter object similar to GET requests (e.paremeter.name or e.parameter.age).

  3. postData - The contents property of the postData object includes the POST body and type property of postData specifies the MIME type of the post body. It can have values like application/x-www-form-urlencoded (key-value pairs separated by the ’&’ character and each key is separated from its encoded value by ’=’), application/json for JSON data or text/plain for text body.

For binary data, like file uploads, the HTTP post request is sent with the multipart/form-data mime type. In the case of application/x-www-form-urlencoded, the queryString is set as part of the POST request body.

const doPost = (request = {}) => {
  const { parameter, postData: { contents, type } = {} } = request;
  const { source } = parameter;

  if (type === 'application/json') {
    const jsonData = JSON.parse(contents);
    return ContentService.createTextOutput(JSON.stringify(jsonData));
  }

  if (type === 'application/x-www-form-urlencoded') {
    const json = {};
    contents
      .split('&')
      .map((input) => input.split('='))
      .forEach(([key, value]) => {
        json[decodeURIComponent(key)] = decodeURIComponent(value);
      });
    return ContentService.createTextOutput(JSON.stringify(json));
  }

  return ContentService.createTextOutput(contents);
};

Testing HTTP Requests with Google Scripts

You can use Postman, RequestBin, CURL or any of your favorite dev tool to send GET and POST requests to your Apps Script service. We’ll use Apps Script itself with the built-in UrlFetchApp service to test the request and response.

Working with HTTP GET Requests

In this example, the GET API coverts the query string to JSON. The test function makeHttpGetRequest compares the supplied query string value with the returned object.

const doGet = (event = {}) => {
  const { parameter } = event;
  const { name, country } = parameter;
  return ContentService.createTextOutput(
    JSON.stringify({ name, country })
  ).setMimeType(ContentService.MimeType.JSON);
};

const makeHttpGetRequest = () => {
  const queryString = '?name=Amit+Agarwal&country=India';
  const apiUrl = ScriptApp.getService().getUrl();
  const url = apiUrl + queryString;

  const options = {
    method: 'GET',
    followRedirects: true,
    muteHttpExceptions: true,
    contentType: 'application/json',
  };

  const response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() == 200) {
    const { country } = JSON.parse(response);
    Logger.log('Country', country);
  }
};

Working with HTTP GET Requests

The doPost method returns either the country or the name from the request body depending on the action parameter of the script URL.

const doPost = (request = {}) => {
  const { parameter, postData: { contents, type } = {} } = request;
  const { name, country } = JSON.parse(contents);
  if (parameter.action === 'getCountry') {
    return ContentService.createTextOutput(country);
  } else {
    return ContentService.createTextOutput(name);
  }
};

const makeHttpPostRequest = () => {
  const url = ScriptApp.getService().getUrl() + '?action=getCountrdy';

  const payload = {
    name: 'Amit Agarwal',
    blog: 'www.labnol.org',
    country: 'India',
  };

  const options = {
    method: 'POST',
    followRedirects: true,
    muteHttpExceptions: true,
    payload: JSON.stringify(payload),
  };

  const response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() == 200) {
    Logger.log(response.getContentText());
  }
};

POST Request with HTML Forms

The next example uses a simple HTML form that sends a POST request with application/x-www-form-urlencoded mime type.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width" />
  </head>
  <body>
    <form
      action="https://script.google.com/macros/s/#####/exec"
      method="POST"
      target="_blank"
    >
      <input type="text" name="name" />
      <input type="text" name="country" />
      <button type="submit">Submit</button>
    </form>
  </body>
</html>

The POST method returns the POST body of the request.

const doPost = (request = {}) => {
  const { postData: { contents, type } = {} } = request;
  return ContentService.createTextOutput(contents);
};

Using CURL to make HTTP Requests

The POST API returns a parameter from the query string of the URL and the name from the request body.

const doPost = (request = {}) => {
  const { parameter, postData: { contents, type } = {} } = request;
  const data = JSON.parse(contents);
  return ContentService.createTextOutput(parameter.secret + type + data.name);
};

You can use CURL to make a POST request to Google Script. Remember to add the -L flag so that curl follows the redirect from script.google.com to googleusercontent.com.

curl -L \
-H 'Content-Type:application/json' \
-d '{"name": "Amit","country": "India"}' \
"https://script.google.com/macros/s/###/exec?secret=1234"

Also see: AJAX, jQuery and Google Scripts

How to Get Hidden and Filtered Rows in Google Sheets with Google Script

Hide Rows in Google Sheets

You can hide entire rows in Google Sheets manually or use filters to hide any rows that matches the specified criteria. For instance, if you have a sheet containing orders from different countries, you can set up a country filter to hide all rows where the country is not the United States.

If you have a Google Script that iterates through each row in the Google Sheet for performing actions on the row, like sending emails or merging documents, you can check for the hidden and filtered rows and easily skip them from the workflow.

There are two ways to check for hidden and filtered rows in Google Sheets. You can either use the SpreadsheetApp service of Google Scripts or use the Spreadsheet V4 API.

Check for hidden rows with Google Scripts

function getHiddenAndFilteredRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var d = 0; d < data.length; d++) {
    // Row Index starts from 1
    if (sheet.isRowHiddenByFilter(d + 1)) {
      Logger.log('Row #' + d + ' is filtered - value: ' + data[d][0]);
      continue;
    }
    // Row Index starts from 1
    if (sheet.isRowHiddenByUser(d + 1)) {
      Logger.log('Row #' + d + ' is hidden - value: ' + data[d][0]);
      continue;
    }
    // processRow(d)
  }
}

The next example uses (ES6 Chrome V8](/es6-google-apps-script-v8-200206). The script fetches all the rows in the currently active Google Sheet and iterates through each of them to finds all rows that are either hidden or filtered.

/**
 * Get the hidden and filtered rows in the specified Google Sheet
 * @param {string} spreadsheetId - Drive File ID of the Google Spreadsheet
 * @param {string} sheetId - The unique ID of the Google Sheet
 * @returns {Array} Index of the hidden rows (first row's position is 0)
 */
const getHiddenRowsinGoogleSheets = (
  spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(),
  sheetId = SpreadsheetApp.getActiveSheet().getSheetId()
) => {
  const fields =
    'sheets(data(rowMetadata(hiddenByFilter,hiddenByUser)),properties/sheetId)';
  const { sheets } = Sheets.Spreadsheets.get(spreadsheetId, { fields });

  const [sheet] = sheets.filter(({ properties }) => {
    return String(properties.sheetId) === String(sheetId);
  });

  const { data: [{ rowMetadata = [] }] = {} } = sheet;

  const hiddenRows = rowMetadata
    .map(({ hiddenByFilter, hiddenByUser }, index) => {
      return hiddenByUser || hiddenByFilter ? index : -1;
    })
    .filter((rowId) => rowId !== -1);

  return hiddenRows;
};

In order to use the Spreadsheet service in your Google Apps Script project, go to Resources > Advanced Google Services and enable the Google Sheets API.

Alternatively, you may enable the Sheets API directly in your appsscript.json file.

  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "Sheets",
      "serviceId": "sheets",
      "version": "v4"
    }]
  }

Spreadsheets Quota Limitation

Google Spreadsheets Quota will allow your addon project to make up 100 Spreadsheet reads per 100 seconds and this limit is shared across all users of the project. Thus if your project has too many simultaneous users, the Spreadsheet service may fail with the error:

API call to sheets.spreadsheets.get failed with error: Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per 100 seconds' of service 'sheets.googleapis.com'

To stay withing the quota, you can either cache the results of the expensive getHiddenRows method or use a try-catch block. If the Spreadsheet API fails due to quota error, use the SpreadsheetApp service to check for hidden rows.

Also, a row in the Google Sheet can be filtered and hidden at the same time.

How to Add an XML Sitemap to your Blogger Blog

The XML Sitemap file is like a directory of all web pages that exist on your website or blog. Google, Bing and other search engines can use these sitemap files to discover pages on your site that their search bots may have otherwise missed during regular crawling.

Google XML Sitemaps for Blogger

The Problem with Blogger Sitemap Files

A complete XML sitemap file should mention all pages of a site but that’s not the case if your blog is hosted on the Blogger or blogspot platform.

Google accepts sitemaps in XML, RSS, or Atom formats. They recommend use both XML sitemaps and RSS/Atom feeds for optimal crawling.

The default atom RSS feed of any Blogger blog will have only the most recent blog posts – see example. That’s a limitation because some of your older blog pages, that are missing in the default XML sitemap file, may never get indexed in search engines. There’s however a simple solution to fix this problem.

Generate XML Sitemap for your Blogger Blog

This section is valid for both regular Blogger blogs (that have a blogspot.com address) and also the self-hosted Blogger blogs that use a custom domain (like postsecret.com).

Here’s what you need to do to expose your blog’s complete site structure to search engines with the help of an XML sitemap.

  1. Open the Sitemap Generator and type the full address of your Blogger blog.

  2. Click the Generate Sitemap button and this tool will instantly create the XML file with your sitemap. Copy the entire text to your clipboard.

  3. Next, go to your Blogger.com dashboard, navigate to Settings –> Search Preferences, enable Custom robots.txt option (available in the Crawling and Indexing section). Paste the XML sitemap here and save your changes.

Blogger XML Sitemap

And we are done. Search engines will automatically discover your XML sitemap files via the robots.txt file and you don’t have to ping them manually.

Internally, the XML sitemap generator counts all the blog posts that are available in your Blogger blog. It then splits the posts in batches of 500 posts each and generates multiple XML feed for each batch. Thus search engines will be able to discover every single post on your blog since it would be part of one of these XML sitemaps.

PS: If you have switched from Blogger to WordPress, it still makes sense to submit XML sitemaps of your old Blogspot blog as that will aid search engines discover your new WordPress blog posts and pages.

How to Track Unsubscribes with Mail Merge for Gmail

Mail Merge for Gmail gives you can easy option to add an “Unsubscribe” link to all your outgoing email campaigns. When an email recipient clicks the unsubscribe link, the status of their email address is set to UNSUBSCRIBED and you will not able to send email campaigns to unsubscribed users in your future mail merge campaigns.

You can also manually resubscribe any unsubscribed contact if they have unsubscribed from your campaigns by accident.

Inside your Google Sheet, go to Addons > Mail Merge with Attachments > Configure Mail Merge and expand the section that says “Enable Email Tracking”;

Provide a Campaign Name, check the option that says “Include an Unsubscribe Link” and also provide the text of the unsubscribe link.

Mail Merge Unsubscribe Link

How Unsubscribe Works in Mail Merge

When the unsubscribe option is checked, all outgoing emails sent through Gmail Mail merge will include a remove link in the message footer automatically.  The subscriber can click the link to opt-out of your future mailing lists.

When they click the unsubscribe link, they are taken to a special landing page confirming that their request for unsubscribing has been successfully recorded.

How to View Unsubscribed User Reports

Go to Add-ons > Mail Merge with Attachments > Campaign Reports > Open and Click Reports. This will open a list of email campaigns you’ve sent through Mail Merge. Click the Unsubscribe button in any specific campaign to generate a list of all email addresses that have unsubscribed from your mailing list via that particular campaign.

This will add a new “Unsubscribe Report” sheet in your Google Spreadsheet for Mail Merge. The report will include the following data to help you analyze the campaigns that resulted in the most unsubscribes:

  • Date when the message was sent to the user
  • Date when the user unsubscribed from the mailing list
  • Email address of the unsubscriber
  • Location / IP address from where the user unsubscribed
  • Web browser (or mobile browser) of the unsubscriber.

Here’s a sample unsubscribe report generated by Mail merge.

unsubscribe-report.png

How to Resubscribe an Unsubscribed Email Address in Mail Merge

The Mail Merge for Gmail app lets you include an unsubscribe link in your email message. If a user clicks the unsubscribe link to opt-out of your mailing lists, you’ll not be able send future email campaigns to that email address.

You can always send emails to the unsubscribed user manually via Gmail but they will not be included in the email campaigns sent via Mail Merge for Gmail.

Reactivate an Email Address

If your contact has unsubscribed their email address from your mailing list accidentally, or if you manually unsubscribed a user from your email system, you can easily reset their subscriber status from withing the Mail merge app. You’ll then be able to resume sending email campaigns to the resubscribed user via Mail Merge.

Resubscribe email addresses manually

Resubscribe Contacts in Mail Merge

Open your Google Sheet, go to the addons menu, select Mail Merge with Attachments, choose Email Campaign Reports and then choose Resubscribe Contacts.

On the next screen, type one or more email addresses that you wish to reactivate for Mail Merge. You can specify up to 4 different email addresses, and each email address should be specified in its own input box.

Click the Resubscribe button to reactivate the subcriber.

Reactive Email Address

How to Embed Images from Google Photos into your Website

Google Photos is the best service for backing up your digital photos to the cloud. They have no storage restrictions, you can upload images as well as videos, and the built-in visual search engine helps you find photos by faces or objects in the picture. There’s one feature though that’s still missing in Google Photos.

You can easily share your photos with anyone using a simple link but Google Photos offers no option for you to embed an existing image into a website. That is, if you have already uploaded an image onto Google Photos, you can’t directly embed it into your website through Google Photos.

Google Photos as an Image Host

Embed Google Photos is a new web app that, as the name suggests, makes it extremely easy for you to pick any image hosted on Google Photos and place it on a web page using simple HTML code.

Here’re the steps involved:

  • Go to photos.google.com and open any image that you wish to embed in your website.
  • Tap the Share Icon (video tutorial) and then click the Get Link button to generate a shareable link of that image.
  • Go to j.mp/EmbedGooglePhotos, paste that link and it will instantly generate the embed code for your selected picture.

That’s it. Open your website template, paste the generated code and save (see sample). The image will now serve directly from your Google Photos account. This technique can also be used for embedding images in HTML Mail without having to use an external image hosting service.

Embed Google Photos

Embed Google Photos - How it works?

When you share any single photo in Google Photos, it creates an unlisted link that is accessible to anyone including those who are not logged into their Google Accounts. Internally, the embed app downloads the page behind this link and extracts the Open Graph tags to determine the direct link of the image and the underlying photo album.

Change Height and Width of the Image

All images hosted inside Google Photos have a URL in a particular format:

https://lh3.googleusercontent.com/xyz=w2400

The w2400 in the URL indicates that the maximum width of the image will be 2400 pixels. However, if you have a higher resolution image, you can change the width parameter to something like w8000 meaning 8000 px wide. The height will be adjusted automatically to preserve the original aspect ratio.

You can also specify the height and width values in the URL and Google Photos will fit the image to the specified size.

https://lh3.googleusercontent.com/xyz=w1415-h944

The embed app only works for single images and not albums. One more thing. I am not aware of any bandwidth limitations for images shared via Google Photos.

Also see: Google Photos - The Good Parts

How to Use Formulas with Google Form Responses in Sheets

When people submit your Google Form, a new row is inserted in the Google Sheet that is storing the form responses. This spreadsheet row contains a Timestamp column, the actual date when the form was submitted, and the other columns in the sheet contain all the user’s answers, one per column.

You can extend the Google Forms sheet to also include formula fields and the cell values are automatically calculated whenever a new row is added to the sheet by the Google Form. For instance:

  • You can have an auto-number formula that assigns an auto-incrementing but sequential ID to every form response. It can be useful when you are using Google Forms for invoicing.
  • For customer order forms, a formula can be written in Google Sheets to calculate the total amount based on the item selection, the country (tax rates are different) and the quantity selected in the form.
  • For hotel reservations forms, a formula can automatically calculate the room rent based on the check-in and check-out date filled by the customer in the Google Form.
  • For quizzes, a teacher can automatically calculate the final score of the student by matching the values entered in the form with the actual answers and assigning scores.
  • If a users has made multiple form submissions, a formula can help you determine the total number of entries made by a user as soon as they submit a form.

Autofill Google Sheets Formulas

Google Sheets Formulas for Google Forms

In this step by step guide, you’ll learn how to add formulas to Google Sheets that are associated with Google Forms. The corresponding cell values in the response rows will be automatically calculated when a new response is submitted.

To get a better understanding of what we are trying to achieve, open this Google Form and submit a response. Next, open this Google Sheet and you’ll find your response in a new row. The columns F-K are autofilled using formulas.

All examples below will use the ArrayFormula function of Google Sheets though some of these example can also be written using the FILTER function.

Auto-Number Form Responses with a Unique ID

Open the Google Sheet that is storing form responses, go to first empty column and copy-paste the following formula in the row #1 of the empty column.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "Invoice ID",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6)
  )
)

The ROW() function returns the row number of the current response row. It returns 1 for the first row in the Invoice Column and thus we set the column title in the first row. For subsequent rows, if the first column of the row (usually Timestamp) is not empty, the invoice ID is auto generated.

The IDs will be like 00001, 00002 and so on. You only need to place the formula is first row of the column and it auto-populates all the other rows in the column.

The IFERROR function returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent. So in this case 1/0 is an error and thus it always returns a blank value.

Date Calculation Formula for Google Forms

Your Google Form has two date fields - the check-in date and the check-out date. The hotel rates may vary every season so you have a separate table in the Google Sheet that maintains the room rent per month.

Google Sheets Date Formula

The Column C in the Google Sheet holds the responses for the check-in date while the D column is storing the check-out dates.

=ArrayFormula(
    IF(ROW(A:A) = 1,
      "Room Rent",
      IF(NOT(ISBLANK(A:A)),
       (D:D - C:C) *
       VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE),
       ""
      )
   )
)

The formulas uses VLOOKUP to get the room rates for the travel date specified in the form response and then calculates the room rent by multiplying the room rent with duration of stay.

The same formula can also be written with IFS instead of VLOOKUP

=ArrayFormula(
    IF(ROW(A:A) = 1,
        "Room Rent",
        IFS(ISBLANK(C:C), "",
           MONTH(C:C) < 2, 299,
           MONTH(C:C) < 5, 499,
           MONTH(C:C) < 9, 699,
           TRUE, 199
        )
    )
)

Calculate Tax Amount Based on Invoice Value

In this approach, we’ll use the FILTER function and that could lead to a less complicated formula than using using IF function. The downside is that you have to write the column title in row #1 and paste the formulas in row #2 (so one form response should exist for the formula to work).

=ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

Here we apply 35% tax to the invoice value and this formula should be added in the row #2 of the column titled “Tax Amount” as shown in the screenshot.

Assign Quiz Scores in Google Forms

Which city is known as the big apple? This is a short-answer question in Google Forms so students can give responses like New York, New York City, NYC and they’ll still be correct. The teacher has to assign 10 points to the correct answer.

=ArrayFormula(
    IF(ROW(A:A) = 1,
      "Quiz Score",
      IFS(
        ISBLANK(A:A), "",
        REGEXMATCH(LOWER({B:B}), "new\s?york"), 10,
        {B:B} = "NYC", 10,
        TRUE, 0
      )
    )
)

In this formula, we are making use of the IFS function that like an IF THEN statement in programming. We are using REGEXMATCH to match values like New York, New York, newyork in one go using regular expressions.

The IFS function returns an NA if none of the conditions are true so we add a TRUE check at the end that will always be evaluated to true if none of the previous conditions matched and returns 0.

Extract the First Name of the Form Respondent

If you have form field that asks the user to entire their full name, you can use Google Sheets function to extract the first name from the full name and use that field to send personalised emails.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "First Name",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+"))
  )
)

We’ve used RegexExtract method here to fetch the string before the first space in the name field. The PROPER function will capitalise the first letter of the name incase the user entered their name in lower case.

Find Duplicate Google Form Submissions

If your Google Form is collection email addresses, you can use that field to quickly detect responses that have been submitted by the same user multiple times.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "Is Duplicate Entry?",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "")
  )
)

Assuming that the Column B is storing the email addresses of the form respondents, we can use the COUNTIF function to quickly mark duplicate entries in our responses spreadsheet. You can also use conditional formatting in Sheets to highlight rows that are possible duplicate entries.

Email Form Responses with AutoFill Values

You can use Document Studio to automatically send an email to the form respondents. The email is sent after the formular values are auto-filled by the Google Sheet. The original form response and the calculated values can also be included in the generated PDF document.