An organization recently migrated their Word Documents from Microsoft Office to Google Drive. The migration has been smooth but the Word documents imported as Google Docs are using Calibri, the default font family of Microsoft Word.
The company is looking to replace the fonts in multiple Google Documents such that the document headings using Georgia while the body paragraphs are rendered in Droid Sans at 12 pt.
Replace Font Styles in Google Docs
This example show how to replace the font family of your Google Documents of specific sections - the heading titles are rendered in a different font while the tables, list items, body and table of contents are formatted with a separate font.
The company’s handbook is written in Google Docs. The document spans several pages and now the writer has been asked to create links such that all mentions of the company name in the document are linking to the company’s official website.
It can be a time consuming task but with Google Apps Script, specific words in a document can be hyperlinked in bulk in one click.
Add Hyperlinks in Google Docs
This example show how to search and replace all occurrences of a text phrase, the company name in this case, and add links to a specific website.
For the next iteration of the handbook, the company’s name has changed but the website domain is the same. The writer is required to change every instance of the company’s name in the document but the underlying hyperlink should not be modified..
Google Sheets includes built-in functions for converting cell references in A1 notation to row and column numbers and another function for converting column alphabets (like AA) into the column index (26 in this case).
=ADDRESS(23, 28, 4) - Returns the A1 style notation of the cell whose row number is 23 and column number is 28.
=COLUMN(C9) - Returns the column number of a specified cell C9 where column A corresponds to 1 and column AA corresponds to 27.
Get A1 Notation with JavaScript
If you are working with the Google Sheets API, you may sometimes needs to calculate the A1 notation style reference of a cell whose row and column numbers are known in the JSON data of the sheet.
For container bound Google Sheets, the getA1Notation() method can return the range address in A1 Notation.
const sheet = SpreadsheetApp.getActiveSheet();const range = sheet.getRange(1,2);
Logger.log(range.getA1Notation());
If you are not using the Spreadsheet service, you can also compute the A1 notation reference of a cell using simple JavaScript.
/**
*
* @param {number} row - The row number of the cell reference. Row 1 is row number 0.
* @param {number} column - The column number of the cell reference. A is column number 0.
* @returns {string} Returns a cell reference as a string using A1 Notation
*
* @example
*
* getA1Notation(2, 4) returns "E3"
* getA1Notation(2, 4) returns "E3"
*
*/constgetA1Notation=(row, column)=>{const a1Notation =[`${row +1}`];const totalAlphabets ="Z".charCodeAt()-"A".charCodeAt()+1;let block = column;while(block >=0){
a1Notation.unshift(
String.fromCharCode((block % totalAlphabets)+"A".charCodeAt()));
block = Math.floor(block / totalAlphabets)-1;}return a1Notation.join("");};
This is equivalent to =ADDRESS() function of Google Sheets.
Get Column Number from A1 Notation
The next function takes the cell reference in A1 notation and returns the column number and row number of any cell in the spreadsheet.
/**
*
* @param {string} cell - The cell address in A1 notation
* @returns {object} The row number and column number of the cell (0-based)
*
* @example
*
* fromA1Notation("A2") returns {row: 1, column: 3}
*
*/constfromA1Notation=(cell)=>{const[, columnName, row]= cell.toUpperCase().match(/([A-Z]+)([0-9]+)/);const characters ="Z".charCodeAt()-"A".charCodeAt()+1;let column =0;
columnName.split("").forEach((char)=>{
column *= characters;
column += char.charCodeAt()-"A".charCodeAt()+1;});return{ row, column };};
This is equivalent to the =ROW() and =COLUMN() functions available in Google Sheets.
The upcoming release of Document Studio includes support for adding markers in the header, footer and the footnotes section of your Microsoft Word template. The add-on will automatically replace this placeholder text with actual values sourced from Google Sheets or Google Forms.
Replace Header and Footer with Document API
This Apps Script snippet uses the Google Docs API to find and replace multiple blocks of text in the header and footer section of your Google Document. The header and footer sections are children of the parent DOCUMENT section.
If the current document doesn’t include an header section, the getHeader() function will return null so you may wish to include additional checks to determine whether a document has an header or footer.
This Apps Script sample shows how you can programmatically schedule video meetings inside Google Meet with one or more participants using the Google Calendar API. It can be useful for teachers who wish to schedule regular meetings with their students but instead of manually creating meeting invites, they can easily automate the whole process for the entire class.
Setup Google Meeting with Apps Script
Give your meeting a title, the start date, the meeting duration, the list of attendees and how often you wanted to be reminded of the upcoming Google meeting. A new meeting event will be added to your Google Calendar and you’ll also be provided with a Google Meet link that you share with your students and colleagues through mail merge.
constcreateGoogleMeeting=()=>{// The default calendar where this meeting should be createdconst calendarId ="primary";// Schedule a meeting for May 30, 2021 at 1:45 PM// January = 0, February = 1, March = 2, and so onconst eventStartDate =newDate(2021,4,30,13,45);// Set the meeting duration to 45 minutesconst eventEndDate =newDate(eventStartDate.getTime());
eventEndDate.setMinutes(eventEndDate.getMinutes()+45);constgetEventDate=(eventDate)=>{// Dates are computed as per the script's default timezoneconst timeZone = Session.getScriptTimeZone();// Format the datetime in `full-date T full-time` formatreturn{
timeZone,
dateTime: Utilities.formatDate(
eventDate,
timeZone,"yyyy-MM-dd'T'HH:mm:ss"),};};// Email addresses and names (optional) of meeting attendeesconst meetingAttendees =[{
displayName:"Amit Agarwal",
email:"amit@labnol.org",
responseStatus:"accepted",},{ email:"student1@school.edu", responseStatus:"needsAction"},{ email:"student2@school.edu", responseStatus:"needsAction"},{
displayName:"Angus McDonald",
email:"assistant@school.edu",
responseStatus:"tentative",},];// Generate a random idconst meetingRequestId = Utilities.getUuid();// Send an email reminder a day prior to the meeting and also// browser notifications15 minutes before the event start timeconst meetingReminders =[{
method:"email",
minutes:24*60,},{
method:"popup",
minutes:15,},];const{ hangoutLink, htmlLink }= Calendar.Events.insert({
summary:"Maths 101: Trigonometry Lecture",
description:"Analyzing the graphs of Trigonometric Functions",
location:"10 Hanover Square, NY 10005",
attendees: meetingAttendees,
conferenceData:{
createRequest:{
requestId: meetingRequestId,
conferenceSolutionKey:{
type:"hangoutsMeet",},},},
start:getEventDate(eventStartDate),
end:getEventDate(eventEndDate),
guestsCanInviteOthers:false,
guestsCanModify:false,
status:"confirmed",
reminders:{
useDefault:false,
overrides: meetingReminders,},},
calendarId,{ conferenceDataVersion:1});
Logger.log("Launch meeting in Google Meet: %s", hangoutLink);
Logger.log("Open event inside Google Calendar: %s", htmlLink);};
The above code can be extended to create meetings that occur on a recurring schedule.
You need to simply add a recurrence attribute to the meeting event resource that specifies the recurring event in RRULE notation. For instance, the following rule will schedule a recurring video meeting for your Maths lecture every week on Monday, Thursday for 8 times.
Microsoft Word includes a useful “Insert Watermark” feature to help you easily add your brand’s logo image or a text stamp that fades behind the content of every page in the document. A company’s policy may require employees to add watermarks to indicate if any document is in draft stage or if the document is confidential and not meant for external distribution.
Insert Watermarks in Google Docs
Unlike Microsoft Word, there’s no built-in support for Watermarks in Google Docs but there’s a simple workaround - create a faded image with the text of your watermark and place that image behind the text of your document pages. Here’s how:
1. Create the watermark stamp
Launch MS Paint on your computer and create a simple watermark image in landscape mode with dark gray text. Please use a bold font like Impact with large font size as the large image can always be resized inside Google Docs but not vice-versa.
I’ve also added some ready-to-use image stamps on Canva and Imgur.
2. Upload Watermark to Google Docs
Inside Google Docs, go to the Insert menu, choose the Image submenu and select Upload from Computer. Upload the watermark image that you saved in the previous step to Google Docs.
3. Open Image Options
Right-click the uploaded image inside Google Docs and choose Image Options from the contextual menu.
4. Change Rotation
Expand the Image Options sidebar and, under the
Size & Rotation section, set the
angle to around 320° to make the watermark diagonal.
5. Send the Image Behind Text
Under the text wrapping section, choose Behind Text to send the watermark image behind the content of your document.
Under Position, choose the Fixed position option with the layout set as Center. This will position your watermark image right in the center of the page.
Under the Adjustments section, set the transparency level to around 80% to fade the watermark image in the background.
The Watermark Effect in Documents
Here’s how the final watermark effect will look like in your Google Document.
Customers makes the payment and completes the order on our website.
PayPal sends a BILLING.SUBSCRIPTION.ACTIVATED webhook to a serverless function.
The function (running on Firebase, Google Cloud) verifies the subscription and checks if the status is active.
It invokes the Apps Script API to complete the order.
The cloud function was previously using the official PayPal SDK for Node.js but it has been recently deprecated and no longer supports the new PayPal subscriptions API endpoints. Migrating from the PayPal Node SDK to your own solution is relatively simple and involves two steps:
If you are planning to test your integration with your PayPal sandbox account instead of the production version, replace api-m.paypal.com in the requests with api-m.sandbox.paypal.com and use the sandbox client secret credentials.
2. Verify PayPal Subscription
A successful request returns the HTTP 200 OK status code and a JSON response body.
const{default: axios }=require("axios");constverifyPayPalSubscription=async(subscription_id)=>{const token =awaitgetPayPalAccessToken();const options ={
method:"GET",
url:`https://api-m.paypal.com/v1/billing/subscriptions/${subscription_id}`,
headers:{
Authorization:`Bearer ${token}`,
Accept:"application/json",},};const{ status, data ={}}=awaitaxios(options);if(status ===200){const{ subscriber:{ email_address }={}, status }= data;return status ==="ACTIVE";}returnfalse;};
Once the PayPal Subscription is found to be active, an HTTP request is made to the Google Apps Script API that sends the invoice and license to the customer. Learn more.
Here’s a list of my favorite extensions for Google Chrome that I depend on every day. These recommended extensions will improve your productivity and also enhance your overall web browsing experience.
The Best Extensions for Google Chrome
Vimium C — Browse the web, interact with web pages, navigate browser history using keyboard shortcuts. Inspired by Vim commands.
Fika - Read pages in a Kindle-style clean layout with beautiful typography while hiding the distracting parts.
PushBullet — Easily transfer web page links, text notes, or push photos and files from the computer to your phone and vice-versa.
OneTab — Save all your open tabs in a group and duplicate your session anytime later with a click.
Gmail Sender Icons - Quickly identify the sending domain of an email in Gmail without opening the message.
Bubble Cursor - The add-on makes it easy for you to click on links that are too small to select with your mouse or for typing inside input boxes.
Zoho Annotator - A simple ad-free screenshot capture tool with a built-in editor for annotating images. Also integrates with Google Drive, OneDrive and Dropbox.
Loom - Record a screencast video with voice narration, include your webcam video and share instantly. Also see Screenity.
URL Render - Instead of clicking individual links on the Google search page, hover your mouse and instantly view the underlying page in a floating window.
Clipt - A universal clipboard for Android and desktop computers. Copy any text on your phone and instantly paste it on the desktop and vice-versa.
Hover Zoom - Hover your mouse over any thumbnail image on sites like Facebook and Amazon and the add-on will enlarge the image to its full size.
Quick Source Viewer - A better alternative to the native View Source option that displays both JS and CSS files in addition to HTML content.
Save To Drive - Right-click and save the current web page, or images on a page, to your Google Drive.
Google Input - Type text in any language of your choice using virtual keyboards, your handwriting and transliteration.
Google Dictionary - Use this add-on to view definitions of words and learn their correct pronunciation.
uBlacklist - Blocks specific sites and entire domains from appearing in your Google search results.
Gmail Notes - Attach sticky notes to your email message that are persistent and will show up the next time you open that same email inside Gmail.
Tab Notes - A minimalistic start page for Chrome that opens a simple notepad in each new tab where you can quickly jot down your todos and notes.
Simple ToDo - Replace the new tab page of your Chrome with an elegant todo list that requires no signup.
Ugly Email - Some emails contain a tracking pixel that notifies the sender when their emails are read. This add-on blocks the tracking attempts.
Stylebot - Change the appearance of any web page with CSS selectors, permanently hide elements on a page and the changes persist across browser sessions.
Webtime Tracker — Keep track of how you spend your time on individual websites and visualize your Internet usage through graphs.
Skip Search - An address bar shortcut for the I'm lucky command that directly takes you to the most relevant website for your search query.
Single File - Download the entire web page into a single HTML file. The associated CSS files, fonts and images are also saved in the file.
Go Incognito - Open the current tab of your browser in a new incognito tab, useful for browsing news websites are behind paywalls.
Feeder - An excellent RSS feed reader that is accessible from the Chrome toolbar and supports notifications for your favorite feeds.
Scribe - Create step-by-step guides and tutorials by recording steps and publish them as PDF guides.
Visbug - A must-have design tool for web developers that brings powerful editing capabilities to the browser.
Sitemod - Modify any website on the Internet through Chrome Dev Tools and create a permanent copy of the modified website with a unique, shareable URL.
iCloud Passwords - Access your Safari passwords that are saved inside iOS and Mac device from Chrome on Windows PCs.
Twitter Screenshots - Take beautiful, uncluttered screenshots of tweets with a click.
Black Menu - Create shortcuts to your favorite Google services and access your emails, calendar, videos and more in an easily accessible popup.
Notion, my absolute favorite tool for storing all sorts of things from web pages to code snippets to recipes, just got better. They’ve released a public API and thus it will be a lot easier for developers to read and write to their Notion workspace from external apps.
For instance, you can create a document in Google Docs and export it to Notion while staying inside Docs. Google Sheets users can pull pages from Notion database into their spreadsheet. Any new submissions in Google Forms can be directly saved to Notion and so on!
Save Gmail Messages in Notion
I have put together a Gmail add-on that makes it easy for you to save email messages, or any other text content, from Gmail to your Notion workspace with a click. Here’s how the app works.
Step 1: Connect Gmail to Notion
Step 2: Allow Access to Notion pages - if you have multiple databases in your Notion workspace, you have an option to grant access to select databases and the rest will be inaccessible to the external app.
Step 3: Choose Email - open any email message in Gmail and you’ll have an option to edit the content of the email subject and body before sending the content to your Notion page. Please note that the app only supports plain text format at this time.
Step 4: Open Notion - As soon as you hit the Send to Notion button, the content of the currently selected email message is added to your Notion database. You can click the All updates link in your Notion sidebar to view to recently added page.
If you would like to try this Gmail to Notion app, please get in touch.
How to Use Notion with Google Apps Script
If you would to integrate your own Google add-on with Notion API, here’s a brief outline of the steps involved.
Go to notion.so and click the Create New Integration button. You’ll be provided with a Client ID and Client Secret that you’ll need in a later step.
Include the OAuth2 library in your Apps Script project and invoke the getRedirectUri method to get the OAuth2 redirect URL for the previous step.
Connect to Notion API - Make a GetHTTP request to the /vi/databases to fetch a list of all databases that the user has explicitly shared with authorized app.
functiongetDatabasesList(){var service =getNotionService();if(service.hasAccess()){const url ="https://api.notion.com/v1/databases";const response = UrlFetchApp.fetch(url,{
headers:{
Authorization:`Bearer ${service.getAccessToken()}`,"Notion-Version":"2021-05-13",},});const{ results =[]}=JSON.parse(response.getContentText());const databases = results
.filter(({ object })=> object ==="database").map(({ id, title:[{ plain_text: title }]})=>({ id, title }));
console.log({ databases });}else{
console.log("Please authorize access to Notion");
console.log(service.getAuthorizationUrl());}}
Gmail to Notion - Try the App
The Gmail to Notion app is in private beta. If you would like to use it with your Gmail or Google Workspace account, please get in touch for an invite.
The Create React App frameworks lets you easily build single page applications but it doesn’t support multiple entry points. To give you an example, if a website outputs separate home pages for mobile and desktop clients, the pages could be sharing some common React components between them, and it may thus not be practical to build two completely separate React applications.
CRA doesn’t support multiple entry points but there are couple of ways to solve this problem.
Option 1 Eject from the Create React App using the npm run eject command and update the entry inside webpack.config.js file to include multiple entry points.
Option 2 Use an alternate build tool like Vite.js that includes support for multiple entry points out of the box.
Option 3 Use the rewired app - it lets you easily make changes and small tweaks to the default Webpack configuration without ejecting the app.
Option 4 Use REACT_APP environment variables to specify the target component and then use ES5 dynamic imports to load the corresponding app component as shown in this example.
Multiple Entry Points for Create React App
If you intend to use the Create React App configuration without ejecting it, here’s a simple workaround that will help you define multiple entry points and the output will be bundle in separate folders.
A small business maintains their staff roster in a simple Google Sheet - the column A of the sheet contains a list of all employee names and column B contains a list of employees who have been assigned to a project.
The immediate task is to identify staff members who are part of the organization but have not been assigned any project yet. In other words, the manager needs to figure out all employee names from column A who are not preset in column B.
There are two ways to solve this problem - visually and through formulas.
Using Visual Formatting
The first option would be to highlight cells in column A that are missing in column B.
Inside the Google Sheet, go to the Format menu and choose conditional formatting. Here select A2:A for the range field, choose Custom Formula from the Format Cells If dropdown and paste the formula:
=COUNTIF(B$2:B, A2)=0
The COUNTIF function will essentially count the occurrence of each item in Column A against the range of cells in Column B. If the count for an item in Column A is 0, it means that the cell value is not present in column B and the cell is highlighted with a different background color.
Find Missing Items in Another Column
The next approach uses Google Sheet formulas to create a list of items that are in Column A but missing in Column B.
We’ll make use of the FILTER function that, as the name suggests, returns only a filtered version of a range that meets a specific criteria. In our case, the criteria is similar to the one that we used in the visual formatting section.
Go to column C (or any blank column) and enter this formula in the first empty cell.
=FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0)))
The MATCH function returns the position of items in Column A in the range associated with Column B and it returns #N/A if the values is not found. When the result is used with ISNA, it returns true only when the match is not found.
Using Google Query Language
SQL geeks may also use the Google Query Language, we are used it with D3.js visualization, to print the list of names that are in Column B but not in Column B.
=QUERY(A2:A,
"SELECT A WHERE A <> ''
AND NOT A MATCHES '"&TEXTJOIN("|",TRUE,B2:B)&"'
ORDER BY A")
The matches operator in the where clause does a regex comparison and the order by clause in the query will automatically sort the output alphabetically.
The Email Spreadsheets add-on for Google Sheets will help you automate the reporting of spreadsheet data and dashboards by email. If you are an office worker who has been emailing spreadsheets to colleagues manually, this add-on will save you a ton of time. And because it runs on the Google Cloud, your spreadsheet reports will be delivered even while you are offline or on vacation.
With Email Spreadsheets, you can schedule reports and it will automatically send them by email on a recurring schedule. You can email entire workbooks, specific sheets inside a workbook or even range of cells. Watch the video to get started.
Email Google Sheets Automatically
Go to the Google add-on store and install Email Google Sheets. Next, open any Google Spreadsheet in your Google Drive, go to the Add-ons menu inside the sheet, choose Email Spreadsheets from the dropdown and then choose Rules to create your first scheduled email report.
You are presented with a 3-step wizard to help the email schedule of your spreadsheet report.
Step 1: Select Sheet Export Options
Expand the “Select Sheets” dropdown and select one or more sheets that you would like to include in the email. Each sheet is attached as a separate file in the email but you can choose the “Merge all sheets” option to create a single file from all sheets in the workbook.
Select the export format. You can choose between PDF, Excel (xlsx), OpenDocument or CSV formats. The “Email without Attachment” option can be used to embed a specific range of cells in the email body without including any sheet as an attachment.
If you have selected PDF as the export format in step 2, you are presented with a few more options. For instance, you can change the paper orientation (Portrait or Landscape), the paper size or alter the print margins to fit more content on a page. You can choose to show gridlines, notes, sheet names and page numbers in the exported file.
(optional) The Cell Range option lets you specify a range in A1 notation and only that range will be exported in the PDF file.
Tip: Use the Preview button to test how your exported files would be like with the various export options.
Step 2: Write the Email Template
Next, we create an email template that will be sent with your reports. You can specify one or email recipients in the TO, CC, or BCC fields. Multiple email addresses should be separated by a comma.
You can also specify dynamic email recipients based on cell values in the spreadsheet. For instance, if the email address of the recipient is specified in cell B2 of a sheet titled “Employee Shifts”, you can put {{Employee Shifts!B2}} in the To field, and the add-on will pull the dynamic value from the cell at the time of sending the email report.
These dynamic cell values enclosed inside double curly braces can be used inside any of the email fields including subject, email body, and the sender’s name.
The email body can include dynamic cell values as well as ranges that make it easy of you to send portions of the spreadsheet without sharing the full workbook. For instance, you can write {{Employee Wages!B2:F9}} to include only the specific range (B2:F9) from the Wages sheet. Internally, the add-on converts the range to an HTML table, retaining all the display formatting with CSS, and embed it into the email.
You can also include standard HTML tags like H1, IMG, A, B, EM and more to include images and rich formatting in your emails.
Tip: Use the Test Email button to send an email with the exported files before setting up the schedule.
C: Create the Email Schedule
The Google Sheets add-on includes a scheduler to help you set up recurring schedules visually. You can send email hourly, daily, weekly, monthly or even on a yearly recurring basis.
It is also possible to setup advanced schedules like:
Send a recurring email on the last working day of the month.
Send email reports every alternate day and end the reporting after 15 days.
Set up a quarterly schedule and send email reports on the first Monday of the quarter.
That’s it. After specifying the schedule, hit the Save button and your email report will be scheduled.
If you would like to edit your current email report or schedule a new report, go the add ons menu again, choose Email Spreadsheets and Rules.
India is currently in the midst of a second wave of the Coronavirus and this one is far more devastating than what we have seen last year. The country is reporting close to 400,000+ new cases every day but the actual count of daily infections could be much higher.
The COVID-19 vaccination program in India was earlier available to people above 45 years of age but starting May 1st, anyone above the age of 18 years can get vaccinated. That said, vaccine availability remains an issue countrywide and it is cumbersome to manually check slots every few hours.
COVID-19 Vaccines Near Me
The government’s official website - cowin.gov.in - has a useful search section that allows you see the nearby vaccination centers in your city or zip code. You’ll also know how many vaccine doses are available at a specific center and the earliest date when the vaccine slots are likely to open up.
Based on the public CoWIN API, I have developed an open-source vaccine tracker that will automatically monitor vaccine availability near your location and will send email alerts as stocks become available. If you have taken the first vaccine dose already, you can specify your vaccine preference - Covaxin or Covishield - and monitor availability of specific vaccine.
Build your own Covid-19 Vaccine Tracker
Step 1: To get started, click here to make a copy of the Vaccine Tracker Google Sheet in your Google Drive. You should complete this step on a desktop computer as Google add-ons are not available on mobile devices yet.
Step 2: Click the Vaccine Tracker menu (near the Help menu) and choose Enable as shown in the screenshot.
Step 3: You may see an authorization window. If you get an “unverified app” message, click the Advanced link and choose “Go to Vaccine Alerts”. The app is 100% safe and open-source.
Step 4: Go to Step 2 now and choose the Enable menu again to launch the tracker. Enter one more pin codes (comma separated), the email address where you wish to receive the alerts and the age group for which you need to monitor vaccine availability.
You can specify the start date and vaccine availability will be checked only after that date. This is useful for people who have been monitored the first dose and need to find a slot after 4-6 weeks for the second vaccine dose.
The vaccine availability is checked every day by default but you can change the frequency to every 4 hours or every hour.
Click the Create Email Alert button and your system is up and running. Google Sheets will run this monitor every day and send an email at 8 am indicating the availability of vaccines in your specified areas.
Here’s a copy of the email sent by the vaccine tracker.
Stop Vaccine Notifications
If you have been vaccinated and would like the vaccine tracker to stop sending you email alerts, here are the steps:
When a user submits your Google Form, the response can be either saved in the Google Form itself or it can be written as a new row in a Google Spreadsheet. Multiple Google Forms can be associated with a single spreadsheet and their responses will be stored in separate sheets of the same spreadsheet.
If you have multiple Google Forms in your Drive that are writing response data to the same Google Sheet, you can use Google Scripts to determine the name of the spreadsheet and the sheet where that form is storing their responses.
Open the Google Script editor, replace the formId with the Id of your Google Form and run the script to get the name of the associated sheet.
functiongetResponseSheetForGoogleForm(){const formId ="<<Google Form Id>>";// Open an existing Google Form by Idconst form = FormApp.openById(formId);// Are the form responses stored in Google Sheetsconst destinationType = form.getDestinationType();if(destinationType !== FormApp.DestinationType.SPREADSHEET){
Logger.log("This form is not saving responses in Google Sheets");}else{// Get the Id of the response spreadsheetconst destinationId = form.getDestinationId();// Open the Google Workbook and iterate through each sheetconst formSpreadsheet = SpreadsheetApp.openById(destinationId);const[formSheet]= formSpreadsheet.getSheets().filter((sheet)=>{// Returns the URL of the associated Google form// that is sending its user responses to this sheetconst associatedFormUrl = sheet.getFormUrl();return associatedFormUrl && associatedFormUrl.indexOf(formId)!==-1;});
Logger.log(`The form responses are stored in ${formSheet.getName()}`);}}
An international school is building a Google Form where students can register their details. The form would have a drop-down list of countries, the names of class teachers as a multiple choice question and a checkbox style question where students can pick one or more of their favorite subjects.
Adding Bulk Questions in Google Forms
It is easy to create such a form inside Google Forms - here’s a sample form - but there are two issues:
There’s too much data to enter and type in the form. For instance, the country down-down alone has a list of 250 countries and it would take some work for the form editor to manually type each choice in the question.
The question choices in the form may change with time. In the previous example, some teachers may move out, new teachers may join in and the drop-down list in the form has to be updated manually every time there’s a change in the staff.
Auto-Populate Questions in Google Forms with Google Sheets
As with everything else in the world of Google Forms and Google Sheets, we can easily automate the process of adding question choices in Google Forms in bulk with the help of, you guessed it right, Google Apps Script.
The idea is simple. We’ll have a Google Sheet that will be the data source and have all the answer choices for various questions in the Google Form.
The app will read the data from this Google Sheet and auto-populate the choices in the form with one click. You can even create a time-trigger that runs every hour, day or month to dynamically update your form using the most current data available in the spreadsheet.
Add Options in Dropdown Lists & Multiple Choice Questions
Create a Google Spreadsheet and add the question titles in the first row of the sheet, one per column. Next, write down all the options or choices that should be available per question.
Here’s how your spreadsheet structure would look like:
The important thing to note here is that your column headings in the spreadsheet should exactly match the form field labels of the Google Form. The Google Script can bulk add answers in multiple-choice questions with a single answer, drop-down lists and checkbox with multiple options.
Bulk Add Question Choices in Google Forms
Open the Google Sheet that has the question choices, go to the Tools menu and choose Script Editor. Replace the default code in the script editor with the Google Script below. Please watch the video tutorial to know in more detail how this code works.
/**
* Auto-populate Question options in Google Forms
* from values in Google Spreadsheet
*
* Written by Amit Agarwal (MIT License)
*
**/constpopulateGoogleForms=()=>{constGOOGLE_SHEET_NAME="<<Put the name of Google sheet here>>";constGOOGLE_FORM_ID="<<Put your Google Form ID here>>";const ss = SpreadsheetApp.getActiveSpreadsheet();const[header,...data]= ss
.getSheetByName(GOOGLE_SHEET_NAME).getDataRange().getDisplayValues();const choices ={};
header.forEach((title, i)=>{
choices[title]= data.map((d)=> d[i]).filter((e)=> e);});
FormApp.openById(GOOGLE_FORM_ID).getItems().map((item)=>({
item,
values: choices[item.getTitle()],})).filter(({ values })=> values).forEach(({ item, values })=>{switch(item.getType()){case FormApp.ItemType.CHECKBOX:
item.asCheckboxItem().setChoiceValues(values);break;case FormApp.ItemType.LIST:
item.asListItem().setChoiceValues(values);break;case FormApp.ItemType.MULTIPLE_CHOICE:
item.asMultipleChoiceItem().setChoiceValues(values);break;default:// ignore item}});
ss.toast("Google Form Updated !!");};
You need to replace the GOOGLE_SHEET_NAME and the GOOGLE_FORM_ID with your own values. Go to the Run menu inside the Script editor, choose populateGoogleForms function and it should instantly choices for all the specified questions in the Google Form.
Dynamically Update Answers in Google Forms
Going forward, whenever you need to update the answer choices in Google Forms, you can simply update the values inside the spreadsheet and run the same auto-populate function from the Script editor.
Or, to make things even more simple, you can add a button on the spreadsheet page that, when clicked, will automatically update the answers in Google Forms for you.
Go to the Insert menu in Google Sheets, choose drawing and pick any shape. You can also add overlay text to the shape. Once the shape is placed on the spreadsheet canvas, click the menu, choose assign script and type populateGoogleForms.
That’s it. You can click this button to update your Google Forms right within Google sheets. Do note that the script will not append choices, it will replace all existing choices with the ones that are available in your Google Sheet.
The printStackTrace method of Java is useful for handling exceptions and errors during development. It tells you the exact line number in your source code and the file name where the problem occurred.
If you are working in the JavaScript / Google Apps Script world, you can use the console.trace() method to output the complete stack inside the web console ( or StackDriver logs for Google Scripts).
A better alternative is that you parse the stack property of the Error object. This contains the entire stack trace along with line numbers, column position and the function names.
The output of the printStackTrace method looks something like this. The first few lines are the program output and as you move downwards, you’ll see a list of methods which invoked the previous method.
Function One!
index.js:16 Function Two!
index.js:11 Function Three!
index.js:7 three (index.js:12:3)
two (index.js:17:3)
one (index.js:22:3)
index.js:26:3
index.js:27:3
You can use the stack trace to know the exact location of the problematic code in your JavaScript app or if you simply want to print the function calling flow of your JavaScript program without even throwing an exception.
You can bring the power of Google Maps to your Google Sheets using simple formulas with no coding. You don’t need to sign-up for the Google Maps API and all results from Google Maps are cached in the sheet so you are unlikely to hit any quota limits.
To give you a quick example, if you have the starting address in column A and the destination address in column B, a formula like =GOOGLEMAPS_DISTANCE(A1, B1, "driving") will quickly calculate the distance between the two points.
Or modify the formula slightly =GOOGLEMAPS_TIME(A1, B1, "walking") to know how long it will take for a person to walk from one point to another.
If you would like to try the Google Maps formulas without getting into the technical details, just make a copy of this Google Sheet and you are all set.
Using Google Maps inside Google Sheets
This tutorial explains how you can easily write custom Google Maps functions inside Google Sheets that will help you:
Calculate distances between two cities or any addresses.
Calculate the travel time (walking, driving or biking) between two points.
Get the latitude and longitude co-ordinates of any address on Google Maps.
Use reverse geocoding to find the postal address from GPS co-ordinates.
Print driving directions between any points on earth.
Get the address from the zip code itself.
1. Calculate Distances in Google Sheets
Specify the origin, the destination, the travel mode (walking or driving) and the function will return the distance between the two points in miles.
/**
* Calculate the distance between two
* locations on Google Maps.
*
* =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {String} The distance in miles
* @customFunction
*/constGOOGLEMAPS_DISTANCE=(origin, destination, mode)=>{const{ routes:[data]=[]}= Maps.newDirectionFinder().setOrigin(origin).setDestination(destination).setMode(mode).getDirections();if(!data){thrownewError("No route found!");}const{ legs:[{ distance:{ text: distance }}={}]=[]}= data;return distance;};
2. Reverse Geocoding in Google Sheets
Specify the latitude and longitude and get the full address of the point through reverse geocoding of coordinates.
/**
* Use Reverse Geocoding to get the address of
* a point location (latitude, longitude) on Google Maps.
*
* =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
*
* @param {String} latitude The latitude to lookup.
* @param {String} longitude The longitude to lookup.
* @return {String} The postal address of the point.
* @customFunction
*/constGOOGLEMAPS_REVERSEGEOCODE=(latitude, longitude)=>{const{ results:[data ={}]=[]}= Maps.newGeocoder().reverseGeocode(
latitude,
longitude
);return data.formatted_address;};
3. Get the GPS coordinates of an address
Get the latitude and longitude of any address on Google Maps.
=GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
/**
* Get the latitude and longitude of any
* address on Google Maps.
*
* =GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
*
* @param {String} address The address to lookup.
* @return {String} The latitude and longitude of the address.
* @customFunction
*/constGOOGLEMAPS_LATLONG=(address)=>{const{ results:[data =null]=[]}= Maps.newGeocoder().geocode(address);if(data ===null){thrownewError("Address not found!");}const{ geometry:{ location:{ lat, lng }}={}}= data;return`${lat}, ${lng}`;};
4. Print the driving directions between addresses
Specify the origin address, the destination address, the travel mode and the function will use the Google Maps API to print step-by-step driving directions.
/**
* Find the driving direction between two
* locations on Google Maps.
*
* =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {String} The driving direction
* @customFunction
*/constGOOGLEMAPS_DIRECTIONS=(origin, destination, mode ="driving")=>{const{ routes =[]}= Maps.newDirectionFinder().setOrigin(origin).setDestination(destination).setMode(mode).getDirections();if(!routes.length){thrownewError("No route found!");}return routes
.map(({ legs })=>{return legs.map(({ steps })=>{return steps.map((step)=>{return step.html_instructions.replace(/<[^>]+>/g,"");});});}).join(", ");};
5. Measure the trip time with Google Maps
Specify the origin address, the destination address, the travel mode and the function will measure your approximate trip time between the specified addresses, provided a route exists.
/**
* Calculate the travel time between two locations
* on Google Maps.
*
* =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {String} The time in minutes
* @customFunction
*/constGOOGLEMAPS_DURATION=(origin, destination, mode ="driving")=>{const{ routes:[data]=[]}= Maps.newDirectionFinder().setOrigin(origin).setDestination(destination).setMode(mode).getDirections();if(!data){thrownewError("No route found!");}const{ legs:[{ duration:{ text: time }}={}]=[]}= data;return time;};
Tip: Improve Performance by Caching Results
All the above Google Sheets functions internally use the Google Maps API to calculate routes, distances and travel time. Google offers a limited quota for Maps operations and if your sheet performs too many queries in a short duration, you are likely to see errors like ""Service invoked too many times for one day” or something similar.
To get around this problem, it is recommended that you use Apps Script’s built-in cache to store results and, if the results of a function already exist in the case, you’ll make one less request to Google Maps The Maps functions inside this Google Sheet also use caching and here’s how you can implement it.
// The cache key for "New York" and "new york " should be sameconst md5 =(key ="")=>{const code = key.toLowerCase().replace(/\s/g,"");return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key).map((char)=>(char +256).toString(16).slice(-2)).join("");};constgetCache=(key)=>{return CacheService.getDocumentCache().get(md5(key));};// Store the results for 6 hoursconstsetCache=(key, value)=>{const expirationInSeconds =6*60*60;
CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);};/**
* Calculate the travel time between two locations
* on Google Maps.
*
* =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {String} The time in minutes
* @customFunction
*/constGOOGLEMAPS_DURATION=(origin, destination, mode ="driving")=>{const key =["duration", origin, destination, mode].join(",");// Is result in the internal cache?const value =getCache(key);// If yes, serve the cached resultif(value !==null)return value;const{ routes:[data]=[]}= Maps.newDirectionFinder().setOrigin(origin).setDestination(destination).setMode(mode).getDirections();if(!data){thrownewError("No route found!");}const{ legs:[{ duration:{ text: time }}={}]=[]}= data;// Store the result in internal cache for futuresetCache(key, time);return time;};
Websites can determine the visitor’s geographic location using their IP address and serve more relevant content. For example, a weather website may use your IP address to estimate your approximate location and provide weather forecast for your current city automatically. A currency exchange website can determine your default currency based on your country which is detected from your IP address.
There are free web IP lookup services, ip2c.org for example, that will reveal the country of your client’s IP address with a simple HTTP request. We internally use that service at Digital Inspiration to determine the payment service provider on the checkout page.
Bulk IP Lookup with Google Sheets
IP2Location is another good alternative that retrieves more detailed geolocation information for any IP address. The IP location lookup service can retrieve the client’s country, city name, region, the ISP name and more.
If you have a bulk list of IP addresses, you can use Google Sheets to estimate the corresponding geographic details for each of the addresses in few easy steps:
Click here to make a copy of the Google Sheet for performing IP lookups in bulk.
Paste the list of IP addresses in column A, one per row. The lookup service works for both IPv4 and IPv6 addresses.
Enter your key in cell E1. If you have a small list of IP address, use demo as the key or get your own API key from ip2location.com.
Click the Run button, authorize the script and watch as the geographic details and ISP names are populated in the sheet.
How IP2Location Script Works
Internally, the Google Sheet uses the IP2location web service with Google Apps Script to transform IP addresses into geographic region.
It uses the UrlFetchApp service to perform multiple HTTP requests in a single batch for improved performance. Here’s the full source code:
constip2location=()=>{// Get all the input data from Google Sheetconst ss = SpreadsheetApp.getActiveSheet();const data = ss.getDataRange().getDisplayValues();// Use your own API key or use demo keyconst apiKey = data[0][4]||"demo";// Generate API URL for IP addressconstgetUri_=(ipAddress)=>{constAPI_URL="https://api.ip2location.com/v2";return`${API_URL}/?ip=${ipAddress}&key=${apiKey}&package=ws4`;};const requests =[];for(let r =2; r < data.length; r++){const[ipAddress, countryName]= data[r];// Only process rows where the country is blankif(ipAddress &&!countryName){
requests.push({ url:getUri_(ipAddress), rowNumber: r +1});}}// Make API calls in bulk using the UrlFetchApp service
UrlFetchApp.fetchAll(requests).forEach((content, i)=>{// Parse the JSON responseconst{ city_name, country_name, isp, response }=JSON.parse(content);// If the response is populated, the API call failedif(response)thrownewError(response);// Write the response data to Google Sheetconst values =[[country_name, region_name, city_name, isp]];
ss.getRange(requests[i].rowNumber,2,1,4).setValues(values);});// Flush all changes
SpreadsheetApp.flush();};