How marketing teams can use Google Apps Script to automate tasks and expand their JavaScript skills

Ecoregeneration and Tech
Jan 7 · 3 min read

Whether it’s event planning, research aggregation, budgeting, report creation, or reaching out to customers for campaigns; it’s helpful to be able to automate tasks using a script in Google Sheets for example.

And whether you have been wanting to up-level your technical skills by learning JavaScript (a very popular web app language), or just get more work done by reducing manual tasks, Apps Script is a great bridge to both in my opinion.

What is an Apps Script?

An Apps Script can help interconnect Google products (Docs, Slides, Gmail, Calendar, Google Maps, Analytics, etc) and non-Google products together and even schedule activities independently for personal or team-based projects. Google offers a free script editor that requires no software downloads to run them.

The scripts are written in JavaScript, and include built-in functionality such as pulling the spreadsheet a user is on, creating a form, sending an email from a Google Doc template, etc. This makes creating scripts much more accessible for users to build, and can executed from a computer or smart phone since they are online scripts.

Optionally for small apps (that have less than 30 users accessing the app all at once), you can use HTML to create a custom interface if desired.

Getting started with limited coding experience

If you do not feel comfortable writing in JavaScript yet, you can still build things that will help you at work and learn from them in the process. In Google Sheets you can record your steps using a macro, which translates them into code that you can use in your script when it comes to automating chart creation, formatting, importing data from BigQuery (a cloud database that hosts tons of public datasets or you can house private ones if desired), etc. You can also leverage any of the projects listed in this article to copy and modify for your own use cases.

Here are 4 learning projects I made for members of the marketing community:

1) Build a Google Analytics dashboard 🖥️ with an Apps Script, a Sheets add-on, and Google Sites.

  • This is especially useful if you use Google Analytics and want to automate reporting of websites using data such as the number of unique pageviews by page title, location of users in the world, and top browsers. In this article you learn to use a macro to build a script and a custom menu.

2) Auto-calculate rooms needed for a conference 🎟️ in a Google Sheet via an Apps Script

  • Reviews how to make your own formula (called a custom function) to automatically assign what type of room size is needed based on the number of registered participants listed in another column for each session for a large event. You can customize the IF statement for your own tasks.

3) Send SMS 📲 event updates or discount codes using Twilio from a Google Sheet

  • Learn to use Google Sheets to text custom messages using Twilio (an online communications provider). Instructions include how to create a free Twilio account, and how to build a basic interface over Google Sheets using HTML, called a web app.

4) Build a web app interface over a Google Sheet

  • Learn how to create an HTML interface to collect phone numbers and are stored in a Google Sheet. You can create the web app for learning purposes and/or sync it with the sheet that sends text messages from project 3.

More project ideas you can learn from

The Apps Script community has been adding more reusable solutions for anyone to use in the G Suite Solution Gallery, which I also highly recommend checking out.

It includes from sending content via emails, equipment requests, tracking YouTube likes, analyzing user sentiment from feedback via ML, etc.

My intention is to be helpful, and if you would like to learn more about what I publish, feel free to follow me on Twitter.

Ecoregeneration and Tech

Written by

Developer Advocate @Google. Vegan. Accessibility to software tech and permaculture knowledge 4 all. Decolonize. These are my opinions my friends.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade