With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.
This Google Apps Script introductory post from Ben Collins has been around for a while, but worth keeping in the old link locker as it summarises lots of useful information and links. Click through to the source for more details.
Source: Google Apps Script: A Beginner’s Guide
Learn how the Google Tables Apps Script Bot works and see an example of data enrichment using the Mattermark API.
In this post, I’ll show you how to use the new Google Tables Apps Script Bot. When something happens in your Google Table – a new row, or a value changes – a bot can be set to trigger an Apps Script function. It opens up a world of new possibilities.
While the Tables beta has been announced as joining Google Cloud it is still limited to US users. In the meantime for those outside the US we are limited to watching from the sidelines. Fortunately we can watch through the expert eyes of community members, in this case the esteemed Ben Collins. In this post Ben highlights how the experimental Tables Apps Script integration can be used to develop bots.
Source: Data Enrichment with the Google Tables Apps Script Bot –
Image: Ben Collins
If you have a Nest thermostat at home, you can access it from your Google Sheet by using Apps Script to connect to the Smart Device Management API. It means you can do some cool stuff like build a virtual, working Nest thermostat in your Google Sheet
Source: Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script
Explore the fantastic new Google Apps Script IDE and see features that will make your life easier, whether you’re a beginner or pro developer.
Ben Collins can never be accused of slacking off and this post is the perfect example. As announced on Totally Unscripted the new Apps Script IDE is rolling out and Ben lists 135 features for developers to look out for. View the source post for these features plus a video of Ben highlighting his favorites.
Source: Guide To The New Google Apps Script IDE Covering 135 Features
Learn how to merge cells in Google Sheets, when to use them and when to avoid them [and] finding merged cells in your Google Sheet using Apps Script
Some very useful guidance from Ben Collins on merged cells within Google Sheets. As well as how to merge/unmerge cells Ben also highlights some of the pitfalls when merging cells. The post also includes a very useful snippet for detecting merged cells using .
Source: How To Merge Cells In Google Sheets And When To Be Careful
Tutorial on how to build an Alexa Rank tracker in Google Sheets, using the powerful IMPORTDATA and REGEXEXTRACT functions and Apps Script.
Clever post from Ben Collins highlighting how Google Sheets functions can be used to scrape Alexa tracking data. As a bonus Ben includes a little snippet of Google Apps Script to let you archive data over time.
Source: Alexa Rank Tracker built in Google Sheets with formulas and Apps Script
Learn how to use Google Cloud’s Natural Language API to do sentiment analysis on tickets submitted to a Google Tables Support Issue Tracker. We’ll use Google Tables as the platform for our Support Tracker and Apps Script to connect to the Cloud Natural Language API.
Great example from Ben Collins building on the work of Alicia Williams which shows how you can interact with your Google Tables with Google Apps Script. As part of this solution Ben uses the new Tables Service, which is the latest Advanced Service to hit Google Apps Script, connecting to the Google Cloud Natural Language API. Click through to the source for all the details you need to get started.
Note: Google Tables beta is currently only available in the U.S.
Source: Sentiment Analysis For Google Tables Using Apps Script –
Source: Apps Script V8 Runtime Explained For Non-Professional Developers
SheetsCon 2020 was the world’s first, dedicated online conference for Google Sheets users on 11th – 12th March 2020.
We had 11 world-class experts talk about how they craft solutions using Google Sheets and G Suite.
Over the course of two jam-packed days, we had everything from deep-dive tutorials on specific topics to cutting-edge solutions across different industries. We had 1-on-1 networking opportunities, roundtable discussion rooms with other professionals and an Expo Hall with vendors at the forefront of the Google Sheets movement.
Listen to all the replays and access all the templates when you enroll for free.
Whilst this was Google Sheets focused there are a number of sessions that cover Google Apps Script and G Suite Add-on development. As well as the replays there are a number of linked resources you can also use.
Source: SheetsCon 2020 Replays
The conversation includes Ben’s usage of automated sentiment analysis of feedback from his courses.
Source: JSJ 419: Google AppScript with Ben Collins
Lifelong tech enthusiast. Systems analyst and developer in the insurance industry. Podcast and audiobook connoisseur.
Come and celebrate Google Sheets at SheetsCon 2020, the world’s first, dedicated online conference for Google Sheets users.
Listen to world-class experts talk about how they craft solutions using Google Sheets and G Suite.
Over the course of two jam-packed days, you’ll hear everything from deep-dive tutorials on specific topics to cutting-edge solutions across different industries. You’ll have the chance to network with other professionals and meet vendors at the forefront of the Google Sheets movement.
Looks like a great event being hosted by Ben Collins for Google Sheets users. Click through to the SheetsCon 2020 website for the full agenda and free registration
Source: SheetsCon 2020
Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge…. It would have been too time consuming to reply to all 150 responses manually from my inbox. Since 95% of all my replies would be the same … it was a perfect case for automation.
Ben Collins expertly shares how he automates part of his Gmail inbox to review emails in Google Sheets and prepare draft replies to be sent as responses. As always Ben provides details of each step and the code.
PS Don’t miss Ben’s free Automation With Apps Script course – enrolment closes 31 January 2020.
Source: Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins
Use Google Apps Script to create Google Sheets sort-by-color and filter-by-color features and replicate the functionality that Excel has.
Love the clever workaround Ben Collins uses to let you filter and/or sort a Google Sheet by cell colour. Ben’s post includes all the code you need and an explanation of how it works … or you can just make a copy of the sheet and start using yourself.
Source: Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script
Google Sheets expert Ben Collins shares tips and tricks for getting the most out of G Suite for your personal or business email.
In this interview Ben also shares a number of Google Apps Script based solutions he has developed and shared for managing his inbox including “I wrote a small program that automatically creates a draft reply from each row of my Google Sheet. Based on the sentiment of the response, the program adds a generic thank you message. Now my workflow is to go directly to my draft Gmail folder, review these draft emails (which include the feedback so I don’t even have to look at the Google Sheet) and add a custom response before hitting send”. You can view the interview for a link to the example script Ben shared for this.
Source: How I Email: Ben Collins, Google Sheets Developer & Data Analytics Instructor – The Gmail Genius
Learn how to use Goal Seek in Google Sheets. The Goal Seek feature enables you to change input values to make a formula result match the goal you specify.
We’ve highlighted the new Goal Seek Add-on previous in Pulse, but if you missed it or wanted to see some more practical use cases Ben Collins has expertly covered how to setup and use it.
Source: Goal Seek in Google Sheets – How To Use Goal Seek In Google Sheets