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

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

In this short tutorial, Yagisanatode will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. They even have a how-to on getting the average sentence length in a paragraph.

Source: Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

TSFormBot — A Google Form Hangouts Chat Bot powered by Google Apps Script

TSFormBot is an Apps Script powered Google Form Hangouts Chat Bot which creates form submission webhook notifications for Hangouts Chat Rooms.

Need a structured ping on your Hangouts chat whenever someone submits a form? Laura Taylor has built a perfect solution with an ever more easier workflow documentation on how to implement it.

Source: techstreams/TSFormBot

Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

Ever needed to merge two similar datasets and automatically get rid of any duplicates?

This article describes how to do that using the SsObjects Google Apps Script library.

Source: Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

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)

Google Apps Script: Add and removed Google Sheets columns based on a search array

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Source: Google Apps Script: Add and removed Google Sheets columns based on a search array

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

Plan an Educational Visit in under two minutes using Google Apps Script

This video demonstrates how teachers can plan an educational visit in under two minutes using Google Witchcraft and Wizardry (also known as Google Apps Script!)

A creative approach on automating the process of filling a Google Form towards an education visit and creating a Calendar invite with all the relevant information.

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

AutoSlides | A multi-functional tool that refreshes Google Slides automatically without reloading

AutoSlides is a Google presentation template powered by Apps Script code design to be used in unattended information points, although it can also be used as an informational element, linked or embedded, on any website.

Google presentations have a publishing feature that facilitates their dissemination. In this way they can be linked or embedded in web media with ease. However, if the presentation slides are modified it is necessary to reload the page that contains its versions published in the browser so that these changes are manifested. AutoSlides is capable of generating a self-hosted slide show, visible using a browser, that automatically refreshes at predetermined intervals without reloading the web page manually in the browser.

Source: pfelipm/autoslides

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

How to Scrape Reddit with Google Scripts – Digital Inspiration

Learn how to scrape data from any subreddit on Reddit including comments, votes, submissions and save the data to Google Sheets

Here’s Google script that will help you download all the user posts from any subreddit on Reddit to a Google Sheet. And because it’s using pushshift.io instead of the official Reddit API, the script will no longer be capped to the first 1000 posts. It will download everything that’s ever posted on a subreddit.

Source: How to Scrape Reddit with Google Scripts – Digital Inspiration

Creating a Slackbot to Scan Google Ads URLs for Errors

As an agency dealing with tens of thousands of URLs, it’s inevitable that some of them are going to break without our knowledge.

Not only does sending traffic to a broken link waste ad spend, it also leads to a poor experience for the user. Redirecting links can also strip out tracking code, preventing us from accurately seeing the impact of our activity. In order to mitigate this, we’ve set up a Google Apps Script to automatically scan all active ads in our MCC account and send a notification to Slack (or via email) whenever it detects a problem.

Source: Creating a Slackbot to Scan Google Ads URLs for Errors

Subscribe to Apps Script Pulse...