Phew — after two days of trying to find a solution for how to accomplish a popular, reasonable set of tasks in Notion’s API, I wanted to do my duties as a Google Developer Expert and blog the solution for others to use.
A familiar story for many developers is getting stuck on a problem. Max Brawer takes some time out to share how he solved a recent problem interacting with the Notion API with Google Apps Script. Max’s post includes lots of great tips for unpicking the steps required to get Apps Script talking to Notion.
While there is not way to directly duplicate Filter Views into other sheet tabs in Google Sheets we can do this with a little bit of Google Apps Script magic.
In this tutorial, we will walk through how to duplicate all filter views from a source Google Sheets tab and duplicate them into selected sheets tab.
We’ll start off with an example sheet to better understand what we are going to achieve and then share the code along with a quick-use guide for those of you who want to to just get in and use the script in your own project.
This tutorial from Scott Donald is a great opportunity to learn more about manipulating Filtered Views in Google Sheets and as a bonus you may pick up some tips on how to use the Sheets Advanced Service.
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.
I’m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization
Google Apps Script includes an Advanced service to interact with BigQuery. A limitation with both the Workspace services and Advanced Services don’t play nice if you need to use a service account. This post from Bruce Mcpherson provides details of how you can setup your Google Apps Script project to use a service account with BigQuery.
Use Google Apps Script to Autofill a formula down rows in a Google Sheet.
Autofill Google Sheet Formula
The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.
All PDF settings including colontitles = custom headers and footers.
Input parameters is a single plain object.
The library uses the printing features of Google Spreadsheets to provide a complete representation of a document in a different format. It contains a huge amount of features There are settings such as page size, headers and footers, colontitles, gridlines, notes and more.
This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.
Handy little code pattern for clearing ranges across multiple tabs in Google Sheets. Snippets are provided for both SpreadsheetApp and the advanced Sheets service.
Convert selected range into PNG image and save it to Drive
In this post from Max Makhrov you can discover how you can convert a selected Google Sheets range to an image. With options to embed charts and tables in Google Docs and Slides you might be asking why you would want to do this. Max highlights a couple of great reasons:
You need to create a screenshot and use it later: send it to Telegram or email, or use it in Slides.
You want to create an image of cells as you see them on screen, containing images, charts, sparklines, etc.
You want to create a screenshot of a range that does not fit your screen.
You want to create a screenshot of a range with high resolution.
There are some limitations with this approach which are outlined in the post and if you have any solutions there is a link to contribute to the GitHub repo.
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.
