AppsScriptPulse

Beginner, Sheets

How to combine arrays of data into one Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.

Source: How to combine arrays of data into one sheet – this post is also available to read on bazroberts.com

No Code, Sheets

11 new analytical functions in Google Sheets for 2023

Tags:
| | 0 Comments |

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

Solutions, Sheets, Intermediate

Custom function for get subtotals for each group in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

You get subtotal sums for each of the selected columns.

The code speaks for itself in this post from Max Makhrov in which he shows how to create a custom function for Google Sheets to subtotal groups of columns. There is some impressive data manipulation/reshaping going on in the function and some nice use of using arrays in formula created with brackets { }. Some of the code is also written with OpenAI/ChatGPT (I’m guessing anything with a for loop). The icing on top is the conditional formatting for group totals. Follow the source link for all the code.

Source: Subtotals for each group in Google Sheets

Solutions, Sheets, Intermediate

Programmatically handle long running Google Sheets tasks with Google Apps Script

Tags:
| | 0 Comments |

 

Image credit: Sourabh Choraria

An approach to process hundreds of thousands of rows of data (say, on Google Sheets) without running into the script execution timeout error.

TL;DR — the typical challenge we face with tasks that take longer to execute is that of running into the execution timeout error (that’s at 6 minutes — Script runtime — as of 10-Feb-2023) and this article … elaborates on how to bypass script execution timeout with a detailed example.

As noted in this post by Sourabh Choraria the Apps Script runtime limit can be a bit of a challenge. If you’re encounter problems my first tip would be review your script and see what you can optimize. A common problem I often see in Google Sheets is using .getValue()/.setValue() (singular) not  .getValues()/.setValues()  (plural).

If even with script optimization you are still having issues Sourabh provides a very adaptable ‘design pattern’ for Google Sheets. The pattern is one I can personally relate too and very similar to the approach I used in my own projects. The source post has everything you need to try this out for yourself.

Source: Programmatically handle long running tasks in Apps Script

Solutions, Sheets, Advanced

An introduction into using Cloud Run to control Google Sheets - Part 1

Tags:
| | 0 Comments |

 

Image credit: Google

Google Apps Script is great and all (and it’s a great starting point for writing code to control spreadsheets) but it doesn’t do all of the things I want it to. Namely, it doesn’t run Python, only Javascript (actually App Script but it’s a small distinction).

We are going to write some App Script in this series, but it won’t be the powerhouse of our logic. Instead, we will use it to capture user input and proxy requests to our own API.

If you’ve spent a bit of time with Apps Script it’s very likely that you’ve used SpreadsheetApp or the Sheets advanced service to interact with Google Sheets. If you are interested in expanding your horizons and using different tooling for this job here’s a starter tutorial showing you how you can spin up a Google Cloud Run service using Terraform which will let you deploy a Python API application.

Source: Cloud Run for Google Sheets — Part 1

Solutions, Sheets, Intermediate

Using design patterns in Google Apps Script

Tags:
| | 0 Comments |

Google Apps Script is a JavaScript-based language that has access to Google Workspace-specific libraries for Gmail, Google Sheets, Google Forms, Google Drive, etc., and allows you to quickly and efficiently automate your tasks and program business applications.

A lot of users try and quickly learn GAS and use it to make their lives easier. It’s all great, however the code we sometimes tend to come across on StackOverflow and other sites lacks best practices, hence I thought it was time to start bringing them up and I will start today with design patterns.

I’m all for copy/paste coding and it one of the things I love about the Google Apps Script developer community, there are lots of great snippets out there and in Pulse we’ve now over 800 posts and counting. When you start going beyond quick script solutions into more complex projects investing time planning how you’ll structure your code can save you headaches and frustrations further down the line.

Using design patterns are one way to produce better code that is more readable which in turn is more maintainable and can lead to faster development. This post from Dmitry Kostyuk a nice opportunity to learn about a design pattern for a very common use case of maintaining data in a Google Sheet from a third party API.

Source: Using Design Patterns in Google Apps Script

Official News, Solutions, AppSheet, No Code

How AppSheet reached new heights in 2022 as part of Google Workspace

Tags:
| | 0 Comments |

Image credit: Google

As the way we work continues to change, the need for no-code and low-code tools that enable hybrid work and empower the workforce across all industries is on the rise. The democratization of software creation is also gaining ground within organizations, and by 2024 more than 65% of applications will be developed by low-code tools.

I was late to AppSheet, my journey only really starting in May 2022. Like all platforms, even no/low-code, there is a bit of a learning curve to get your head around how it works, but once you do there is a world of opportunities for a wide range of users. Even for seasoned developers features like the Apps Script integration and AppSheet API mean there is plenty of scope to extend the capabilities of AppSheet.

This post from Google provides some useful reference cases, evidencing the impact the platform has had on a range of customers as well has highlighting some recently developments in the AppSheet platform including the opportunity to deploy AppSheet powered Google Chat apps.

At CTS, where I work, we have a range of services to support AppSheet adoption ranging from hackathons, training and development. Feel free to get in touch if you would like to find out more.

Source: How AppSheet reached new heights in 2022 as part of Google Workspace | Google Workspace Blog

Solutions, Drive, V8, Intermediate

Get the Creator’s email of a Shared Drive with Google Apps Script

Tags:
| | 0 Comments |

 

Learn how the access the creator’s email of a Google Shared Drive with Google Apps Script using the Drive Activity API & Admin Directory SDK.

Scott Donald has found a clever way to get the Shared Drive creator using Apps Script and the Google Drive Activity API. Whilst there is a Google Drive ‘Drives’ endpoint the response doesn’t include the creator in the Drives Response object. This post is a nice example of how you can combine data from different Google Workspace APIs. Follow the source link for a detailed explanation.

Source: Get the Creator’s Email of a Shared Drive with Google Apps Script – Yagisanatode

Add-on, Solutions, Beginner

Restricting the number of times an account can use a feature in a Google Workspace Editor Add-on with Google Apps Script 

Tags:
| | 0 Comments |

One model of monetization for a Google Addon is to allow a certain number of free uses before restricting that feature. This post shows one way to restrict a feature in a Google Editor Addon sidebar.

John McGowan is continuing his Google Workspace Add-on development tips at pace. You can read the story so far on the Automagical Apps Blog. In the latest post you can find out how John uses the Properties Service, to record the number of times an account has used a feature in your add-on by communicating between the sidebar and Apps Script using google.script.run. A reminder as well that you can see how you can boost User Property read/write with the SpeedStore library.

Source: Restricting the number of times to use a feature in a Google Addon Sidebar | Automagical Apps Blog

Snippet, Solutions, Forms, Intermediate

Opening and closing Google Forms on time trigger using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for opening and closing Google Forms on time using Google Apps Script.

Here’s a handy little snippet if you would like to programmatically open/close one of your Google Forms to responses for specific hours of the day. The script includes another trigger that will repeat opening/closing the Google Form for responses each day. As this snippet uses .timeBased().at(date)  it’s easy for you to modify if you want to only have the form open to responses between two specific dates/times.

Source: Opening and Closing Google Forms on Time using Google Apps Script