AppsScriptPulse

11 new analytical functions in Google Sheets for 2023

Get the lowdown on the 11 new analytical functions introduced to Google Sheets in 2023, including LET and array manipulation functions.

For developers Google Sheets is becoming an increasingly rich playground. Features like the SQL like QUERY function have enabled users to do more with their data. More recently Google have released a range of new functions which I’m sure many developers and power users will be able to relate to. New functions like EPOCHTODATE, LET as well as other recent additions like MAP and REDUCE will be familiar concepts to many developers. In this post from Ben Collins the latest Google Sheet functions are reviewed.

Source: 11 New Analytical Functions In Google Sheets For 2023

Google Sheets gets named functions, XLOOKUP and more

In August 2022, Google announced named functions and 9 other new functions in Google Sheets. Named Functions let you save and name your own custom formulas, built with regular Sheets functions, and then re-use them in other Google Sheet files. It’s a HUGE step toward making formulas reusable. Learn about them all here!

Ben Collins is incredibly talented at communicating technical information in a non-technical way. This post is a great example where Ben covers the latest Google announcement on 10 new Google Sheets functions. The two standout ones are Named Functions which lets you make your own reusable function of functions, and something I’m sure many MS Excel users would appreciate, XLOOKUP.

Source: New Functions In Google Sheets For 2022

How to track link clicks in emails and more with Google Apps Script and Google Sheets

Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system

You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.

A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.

Source: How To Track Link Clicks With Apps Script And Google Sheets

A beginners API authentication cheat sheet for Google Apps Script

See four different API Authentication methods presented in Apps Script, including authentication in query string, headers, and OAuth2.

I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.

It’s not a comprehensive post on how to connect to APIs, instead, it’s a short summary of common protocols for easy reference.(If you’re new to APIs, start with my Apps Script API tutorial for beginners.)

We are currently spoilt for choice with Google Apps Script community contributions. This is a great post from Ben Collins for Google Apps Script beginners highlighting different patterns used to interact with third party websites with APIs.

An API is essentially an interface that can be used by a computer programme to retrieve or interact with another application.

What is an API?

If you would like to find out more about API Ben links to his API tutorial for beginners or I have shared workshop materials for ‘Machina a machina: An introduction to APIs with Google Sheets‘.

Check out the source link for Ben’s cheat sheet and other resources 👇.

Source: API Authentication Cheat Sheet for Apps Script

NEW COURSE: Mastering the QUERY Function in Google Sheets

Image credit: Ben Collins (benlcollins.com)

Master the QUERY function, the most powerful function in Google Sheets, to become a more effective data analyst

Friend of Pulse and Google Sheets guru, Ben Collins, has a new course ‘The QUERY Function in Google Sheets’. The QUERY function it lets you perform various data manipulations making it easy to reshape, aggregate and explore your data in Google Sheets. The course is designed to be suitable for everyone from beginner to advanced who are interested in ways to work more effectively with your data.

If you are not familiar with the QUERY function Ben provides one example of what is possible in Sheets Tip 204: How To Use Dates In The QUERY Function (check the linked post in this Sheet Tip for an example worksheet).

Bonus: Sheets Tip 204 includes a 50% discount on the course valid until Friday 20 May 2022 at midnight EDT.

Finally, if you are interested in using the QUERY language in Google Apps Script it is possible! Below is some code used in this copy of Ben’s example workbook based on:

// based on https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011#sample-script

function myFunction() {
  const doc = SpreadsheetApp.getActive()
  const spreadsheetId = doc.getId(); // or set another Spreadsheet ID.
  const sheetId = doc.getSheetByName('Data').getSheetId(); // or set another Sheet ID from Spreadsheet ID.

  const query = "select C, B where B > date '2000-01-01' and B <= date '2002-12-31'"; // your QUERY

  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}&access_token=${ScriptApp.getOAuthToken()}`;

  const res = UrlFetchApp.fetch(url,);
  console.log(res.getContentText());

  const array = Utilities.parseCsv(res.getContentText());
  console.log(array);

  // SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes if directly adding a spreadsheet ID.
}

Source: The QUERY Function in Google Sheets

How to build an automated PDF report in Google Sheets using Apps Script 

Grab this automated ConvertKit Report in Google Sheets to monitor your email list growth and use it to project your future list growth.

Ben Collins shares the solution he uses to produce a daily ‘ConvertKit’ PDF report. Even if you don’t use ConvertKit this post is still worth a read as the basic model of getting data from a third party API into Google Sheets and sending a PDF summary could come in handy. Perhaps not surprising given this post comes from Ben it includes some clever Google Sheet functions to prepare and shape data.

Source: Automated ConvertKit Report In Google Sheets

Repeatable Task [in Google Sheets]: To Script Or Not To Script, That Is The Question

Image credit: benlcollins.com

A framework for thinking about whether you should automate a repeatable task in Google Sheets with scripts or not

Ben Collins provides some great advice on when you should think about automating tasks in Google Sheets. Click through the source link at the bottom for the full post for some great tips.

A graphic that stood out for me was the XKCD comic strip below.


… the math didn’t quite add up in my head so I double checked

Based on CC-BY-NC https://xkcd.com/1205/

Source: Repeatable Task: To Script Or Not To Script, That Is The Question

Google Apps Script: A Beginner’s Guide

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

Data Enrichment with the Google Tables Apps Script Bot -Ben Collins

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 –

Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script

Image: Ben Collins

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