AppsScriptPulse

Understanding Macros and Apps Script

Macros are small programs that a Google Sheets user can create to complete repetitive tasks. In fact, macros are an excellent way for a user to become familiarized with Apps Script, as a snippet of code is saved in the script editor each time a macro is recorded. Recording a macro is as simple as going to Tools → Macros → Record macro.

Recording macros and assigning shortcuts to them is a great way to execute repetitive tasks and become a more efficient Sheets user. You can also import macros from other workbooks so you can perform the same tasks and assign the same shortcuts across all of your workbooks.

Source: Understanding Macros and Apps Script

Scrape and save data to Google Sheets with Apps Script

If you are looking for a way to get data that is displayed on a website and save it to a Google Sheet every <some time interval>—because a record number of cmd+x and cmd+v keystrokes aren’t really the stats that resonate with you and your friends—please, read on.

A detailed guide by Kamie Robinson on how to scrape data from a website, tabulate that on a Spreadsheet and a sweet tip to backfill your data!

Source: Scrape and save data to Google Sheets with Apps Script

Tuning your Google Apps Script experience (for Google Sheets on steroids)

Are you automating Google Sheets using Google Apps Script? Automation is awesome but doing it with the Google tools can be cumbersome.

Interesting article by José Miguel Gutiérrez that provides some amazing tips on how to improve your coding experience while working with Spreadsheets and Google Apps Script.

Source: Tuning your Google Apps Script experience (for Google Sheets on steroids)

How to automatically pull Facebook Ads data into Spreadsheets

After seeing how to send your Google Ads campaign data to Spreadsheets we will do the same with Facebook Ads. Once again, the idea is to implement a data-driven strategy in campaign performance management. But in my opinion, monthly or even weekly reporting are not enough: it has to be daily. And so that it doesn’t take you 20 minutes a day to export your CSV files and format them, here’s how to automate the boring stuff.

Pretty thrifty approach by Henry-Paté Nicolas on tracking Facebook Ads on Spreadsheets using Google Apps Script.

Source: How to automatically pull Facebook Ads data into Spreadsheets

G Suite Solutions —  Apps Script Powered Workflow Automation

Laura Taylor recently spoke at SheetsCon on the topic of Automation with Apps Script. During the presentation, she walked through a simple approval workflow for a hypothetical small business to demonstrate the power of Apps Script to automate workflow inside G Suite. She also showcased modern ECMAScript features supported by the new Apps Script V8 Javascript runtime.

The article is an overview of the workflow covered in the author’s presentation along with install instructions for trying it out in your G Suite domain.

Source: G Suite Solutions —  Apps Script Powered Workflow Automation

Building your first Hangouts Chat Bot in Apps Script

The task of building a Google Hangouts chatbot can often seem daunting due to the diverse technology options and approaches. What is often not always known is how relatively easy it is to build chatbots by simply using Google Apps Script.

Here’s a step by step guide on how to build your first Google Hangouts chatbot using Apps Script.

Source: Building your first Hangouts Chat Bot in Apps Script

API Calls to ServiceNow from Google Apps Script with OAuth

Have you ever thought that it might be useful to connect a Google Apps Script to your ServiceNow instance? Perhaps you’d like a new way to quickly download a list of configuration items from your change management database to a sheet, or you’d like to upload a selected range of cells from a sheet as new records in SNOW, one record per row?

This guide is focused more on the coding than how to configure your identity provider and ServiceNow.

Source: API Calls to ServiceNow from Google Apps Script with OAuth

Trigger daily scripts to take snapshots and draw charts with Google Spreadsheets

Based on an earlier article on how to live track our investment portfolio performance with Google Spreadsheets, it’s now time to automatically take snapshots in order to be able to visualize the evolution of it.

A short script that takes the current value of your portfolio and copies it into a new row while adding a timestamp – thus allowing the chart to visualize the data on a per-day basis.

Source: Trigger daily scripts to take snapshots and draw charts with Google Spreadsheets

Workflow to collect and approve budgets using Apps Script in Google Sheets

Gathering inputs from multiple sources and then taking action on each becomes taxing when the task needs to be realized with frequency and with multiple stakeholders. An example is quarterly or annual budget approvals for a finance team.

In this article the author will share how to use an Apps Script in a Google Sheet to automatically create a budget submission form that you can share with end users, and when their responses arrive in the sheet, you can collaborate with other reviewers to send emails in bulk depending on whether you are approvingrejecting, or asking for more information about their request. The emails use a Google doc as a template that pulls information from the sheet such as a user’s name, the budget values they entered, or special comments.

Source: Workflow to collect and approve budgets using Apps Script in Google Sheets

Faster budgeting with a Google Apps Script custom function

Companies, organizations, households, students, etc all benefit from budget planning in order to optimize savings, because you forecast your total expenses, and then closely monitor the true cost in order to course correct and you reach a savings goal.

In this article the author walks us through how to create our own custom function so we can prepare our annual household budget by entering our expenses in one row and marking their frequency in another (ex: annual, monthly, weekly, daily, or only one time). When we have rows and rows of expenses with different time periods, creating our own formula with a Google Apps Script helps us save a lot of time.

Source: Faster budgeting with a Google Apps Script custom function

Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets

You may be exporting data from an accounting tool into an .XLS format (Excel spreadsheet) every month, and then manually searching, cleaning, and replacing values. More specifically you probably spend a lot of time finding and replacing duplicates, and performing advanced operations such as a VLOOKUP or a QUERY function in order to pull data from other sheets or digging through records that use unfriendly date timestamps.

In this article TechandEco walks us through how to automate this monthly task by using a Google macro and a custom menu, and invite you to try it out yourself. In the future, you can convert your Excel into a Google Sheet by dragging it or uploading it into a folder in your Google Drive>then second click the file to view its options > open as > Google Sheet.

Source: Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets

Manage event registration with Apps Script, Google Calendar and Google Forms

Manage training or event registration with Apps Script, Google Forms and Google Calendar.

Using Google Calendar to setup a meeting is really easy and works like a charm for business or personal needs. But sometimes you want to manage event registration for an event, a training and in this case you need to share a form and register the participant after the submission. Hopefully Google Apps Script is at the rescue.

Source: Manage event registration with Apps Script, Google Calendar and Google Forms

Automate Adding Members to Google Group from Google Sheets — Google Apps Script

I added a script to Google Sheets that auto added new email addresses to the Google Group every time the sheet was edited.

The author was using Google Group to provide access to their dashboard in Google Data Studio. The members kept adding and the process became tedious. They were already keeping/adding all emails in a Google Sheet for some mapping. So this is what they did.

Source: Automate Adding Members to Google Group from Google Sheets — Google Apps Script

Parsing JSON in Google Sheets with Zendesk and Google Script

For the most part, using ImportJSON works well—it’s a low effort way of pulling data from any API’s GET endpoint and storing that data in a sheet for further parsing and filtering. But, there was a flaw: Zendesk’s API calls are paginated and limited to 100 results per page. That meant that we needed to add a new ImportJSON() formula every 100 rows to get all of our data.

This follow-up guide goes the extra step—not to feed a big JSON file into ImportJSON, but to write a script that does everything, including pagination. And, as an added bonus, is easier on your API rate limit.

Source: Parsing JSON in Google Sheets with Zendesk and Google Script

Automating Academic Reviewer Finding With Microsoft Academic and Google Apps Script

One of the big problems today is finding reviewers. Editors are limited by their own knowledge and various publicly-available datasets (such as Google Scholar, Web of Science, and discipline-specific tools like philpapers.org or thephilosophypaperboy.com), and while especially these latter are useful, they are not tailor-made for reviewer finding. This setup presents a way to speed up peer-review by partially automating finding reviewers for academic papers.

The aim here is to make something that is tailor-made for reviewer finding, that works by taking data from one publicly available dataset (namely that of Microsoft Academic, which is basically Google Scholar but Microsoft), extracting from it a list of possible reviewers, creating a database from that, and letting one query this newly created database to find reviewers for a paper under your editorship.

Source: Automating Academic Reviewer Finding With Microsoft Academic and Google Apps Script

Storing data from the Flutter app → Google Sheets 📊

In this article, we’ll create a sample app which stores Feedback of user from the Flutter app into Google Sheets using Apps Script.

Source: Storing data from the Flutter app → Google Sheets 📊

How to use GCP service accounts with Google Apps Script projects to automate actions in G Suite

A Google Apps Script and Google Cloud Platform tutorial to learn how to use GCP Service Accounts to scale and protect automations

Examples of Google Apps Script being used in an enterprise setting tend to be few and far between which means this tutorial by Jeffrey Daube is an interesting insight to how script projects can be setup to serve the entire company. In particular, Jeffrey details how the use of service accounts can be used so that G Suite documents can be created with a non-human user allowing you to make calls to Google APIs without the usual authentication flow.

Source: How to use GCP service accounts with Google Apps Script projects to automate actions in G Suite

Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets

What if you want to catch up with the numbers regularly, say, in particular areas? Of course you can keep scrolling your phone for it, but there are chiller ways to do so.

In this 3-part series, Claire Chung explores the spread of pneumonia outbreak and explains how you can import & track data with Google Sheets, organize it & send notifications.

Source: Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets.

GGvulnz — How to check your domain and groups settings

This article will highlight how you have to be careful about settings when you make some content available publicly, like Groups or Drive file.

The original article describe how an external person of the domain can subscribe to your Slack company channel by using a Google Groups that is publicly accessible. Stéphane Giron’s article helps answer the question – how to prevent and how to identify if some groups are concerned.

Source: GGvulnz — How to check your domain and groups settings

How to get that inactive Twitter handle you really want using Google Apps Script

You may have seen in the news that Twitter will begin deleting inactive accounts of users who hadn’t logged in within the past 6 months before Dec. 11, 2019.

This is bad news if you happen to be @qwerty123, but great news if — like me — you’ve found the perfect username but some clueless bovine has been accidentally hoarding it for the past 8 years.

In this post Sean Cann shows you how you can use Google Apps Script to detect when your preferred new Twitter handle becomes available.

Source: How to get that inactive Twitter handle you really want

Subscribe to Apps Script Pulse...