AppsScriptPulse

Find precedents of cells with formulas in Google Sheets using Apps Script — part 1

enumerate a range in google sheets using google apps script and use that to find precedents of cells that contain a formula.

Let’s start with the ubiquity of spreadsheets. I’ve long felt that spreadsheets were everywhere but until a post from Hjalmar Gislason I had never seen it in numbers and it made for interesting reading:

  • 1.2 billion people use Microsoft Office (WindowsCentral, March 2016), odds are most of them have at least access to Microsoft Excel.
  • Microsoft believes that 1 in 5 adults in the world use Excel (“What’s new in Microsoft Excel”, Sept 2017)
  • Excel is the number one skill mentioned in job ads, mentioned in approximately 1 in 3 job ads! (Indeed.com Job Trends, June 2017).
  • In 2010, RescueTime found that about 25% of computer users used Excel on a daily basis and that about 2% of all time spent on a computer anywhere was spent using Microsoft Excel.

As useful as spreadsheets are the underlying data structure can cause problems. This is covered in more detail in Patrick Burns’ Spreadsheet Addiction, which amongst other things highlights the ambiguity of value and formula:

Primarily the problem is that some cells have hidden meaning. When you see a number in a cell, you don’t know if that is a pure number or a number that is derived from a formula in the cell. While this distinction is usually immaterial, it can be critical.

The leading example is sorting. When rows are sorted, usually it is desired to sort the numbers as they are. However, it will be the formulas that are sorted. To humans the numbers are the primary thing, and the formulas are just something in the background. To the spreadsheet the formulas are primary, and the numbers are mere results.

This is a long winded way to highlight some work from Sourabh Choraria which is exploring how the relationship between formulas and cells can be extracted in Google Sheets. As part of this Sourabh is looking at how the getFormulasR1C1 method in SpreadsheetApp can be processed to find precedent cells. There are some very funky use of regex to parse formulas which is worth spending some time to digest.

Source: find precedents of cells with formulas in google sheets using apps script — part 1

Get Classroom Data into Google Sheets using Google Apps Script

Image credit: Aryan Irani

In this blog, I am going to show you how to get Google Classroom Data into your Google Sheet using Google Apps Script. Using this, you can keep track of all the classes that you have in your Google Classroom.

This is a nice introductory tutorial from Aryan Irani which looks at exporting basic information about your Google Classroom courses to Google Sheets. The example script imports the main details about your courses but if you are interested in finding out what else is available the official documentation for a course resource outlines what else is available.

Source: Get Classroom Data into your Google Sheet using Google Apps Script

Use a Google Sheets as a database in Google Apps Script without sharing it

Make Google Sheets as your database for your web app or add-on developed in Apps Script without sharing the file. … In order to keep our database secret to end users we will use a Service Account (SA) … This Service Account is not a real account, no Gmail or Drive attached but a service account can access a spreadsheet. We will use this property to manage our data.

As the post author Stéphane Giron indicates before using Google Sheets as a database you need to fully scope out if it’s the right solution for you. For ‘small’ data you can often get away with using Sheets and in this post from Stéphane you can learn how to do it in a way where you can have access control by setting up and using a Service Account.

Source: Use a Google Sheets as a database in Apps Script without sharing it

How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

There have been a few instances in my work where I need to remove a button (more accurately, a button drawing) or and image from a Google Sheets tab once the associated script has been run.

Perhaps we just want the user to run a process on a Google Sheet workbook, just once but not more times. This would be a good case for removing the button or drawing after use.

Note: This tutorial expects that you know how to create a drawing or a button from the Google Sheets drawing tool. 

Some clever Google Sheets pseudo button manipulation shown off here by Scott Donald. If using Apps Script functions assigned to drawings and images is something you are interested by sure to also check out Kanshi Tanaike’s example of ‘Using Google Apps Script to disable custom functions assigned to Google Sheets buttons/images to prevent simultaneous execution‘.

Source: How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

Using spread syntax and destructuring assignment in Google Sheets .getValues() with Google Apps Script

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. … I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.

We’ve featured other coding patterns from Kanshi Tanaike and here is some other nice examples of how you can handle 2D arrays returned by .getValues() , in particular, this post shows a nice way of getting non-adjacent columns in Google Sheets.

Source: Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

Use Google Apps Script to pull all Google Shared Drives and permissions into Google Sheets

If you’re a Google Workspace Super Admin, you’ve probably been thinking of ways to inventory and keep a handle on Google Shared Drives. They are a really great addition to the Workspace offering, but there are still some gaps in reporting & oversight that some people have wanted. This solution uses Google Apps Script and the Drive API to pull information about all the Shared Drives in a domain into a Google Sheet. Then, it uses the Drive API again to loop through all the drives and get the top-level permissions.

For Google Workspace admins Nick Young has provided a nice solution for auditing Google Shared Drives within your Workspace domain. This post covers all the steps to setup, a link to the code on GitHub and some notes on limitations to be aware of.

Source: Use Apps Script to pull all Google Shared Drives and permissions into a Google Sheet

Search Google Drive folders for creation/modified dates using Google Apps Script

Search one level of Google Drive folders and extract creation, last updated and folder names into a Google Sheet.

Enter the Parent folder ID and search the folders within

Enter the Parent folder ID and search the folders within

The following Google Apps Script is designed to search through one level of Google Drive folders and extract the following information into a Google Sheet:

  • The folder name as a direct clickable link,
  • The folder creation date,
  • The folder last updated date,
  • The folder ID.

Source: The Gift of Script: Search Google Drive folders for creation/modified dates

Create multiple labels in Gmail with Google Apps Script

With this reusable script you can create multiple labels in Gmail whenever you need to do so. All you have to do is create the script as shown in the video and then use it to create multiple Gmail labels in one go.

Continuing the Gmail label theme in this latest video tutorial from Chanel Greco you can learn how to batch create Gmail labels from data in a Google Sheet.

How to import PayPal transactions into Google Sheets with Google Apps Script – Digital Inspiration

Learn how to easy import transactions from PayPal into Google Sheets with Google Apps Script. You can import standard transactions, recurring subscriptions and donations.

Even if you aren’t interested in PayPal this post is well worth looking at as the author, Amit Agarwal, is a master when it comes to handling data with Google Apps Script and there is a general patterns of ‘get data from an API, write it to a Google Sheet’ that might be useful for your own projects.

Source: How to Import PayPal Transactions into Google Sheets – Digital Inspiration

💩 ShiitCoin: Putting a blockchain on Google Sheets with Google Apps Script

Some ideas are bad, and then sometimes there are ideas so bad they actually go back around to being genius — we think we’ve come across one of those. Yes! We have an end to end blockchain working entirely off of a google sheet: transaction broadcasting, mining, wallets, gossip — all of it! ShiitCoin is (obviously) a troll project not meant to be rEaL mOnEy 💴 but we’re hoping to farm some internet points there’s some educational value to exploring the barebones of a blockchain which we’ll share here.

In pre-pandemic times I used to give a talk entitled ‘..you can do that with Apps Script’, which includes some more of the extreme examples of projects people tackle with Google Apps Script. This project by nalinbhardwaj (Nalin Bhardwaj) and Adhyyan1252 (Adhyyan Sekhsaria) definitely falls into the fun/interesting category and who knows you may find some interesting code snippets in the associated Github repo that might be useful … but no guarantees.

Via Andrew Roberts

Source: ShiitCoin: Putting a blockchain on Google Sheets

Subscribe to Apps Script Pulse...