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

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, 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

Snippet, Beginner, Maps

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

Tags:
| | 0 Comments |

 

Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.

I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.

The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Solutions, Sheets, V8, Intermediate

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Tags:
| | 0 Comments |

ChatGPT is pretty much a universal API: one endpoint to get any data.

Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.

We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

New IDE, Solutions, Docs, Intermediate

Quick backup solution for Google Apps Script by creating versions in Google Docs

Tags:
| | 0 Comments |

Get security and peace of mind by backing up your Google Apps Script files to Google Docs and trigger for continuous backup.

We’ve featured a couple of methods for restoring Google Apps Script code in Pulse, but most of these rely on recovering a deployed version of the script. Stéphane Giron wields his Apps Script hammer and skilfully demonstrates how you can use Google Docs and it’s built-in version history as a way of keeping a backup copy of your code.

Source: Quick and not so Dirty backup solution for Google Apps Script Code

Gotchas, V8, Intermediate, Sheets

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Tags:
| | 0 Comments |

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script

Community News, Beginner

Tech Recruiter Tries Coding pt. 2 — Google Apps (Type)Script

Tags:
| | 0 Comments |

Image credit: Unknown

Talking CRMs, Databases, and how to ditch them; this time within the Lead Generation domain. Bonus: pitfalls of a 90s computing mindset! 😅

Previously on Pulse we shared Rafael Romo Mulas’ post, Tech Recruiter Tries Coding. Rafael has recently published part 2 in which they highlight their journey into VS Code, clasp and TypeScript. If you are unfamiliar with TypeScript its a language which transpiles into JavaScript and basically designed to help you write better code. Rafael notes:

… the main difference is you are forced to be more precise, which avoids running into stupid errors! Therefore, to my surprise, it is actually easier to code in TypeScript than JavaScript, even more so for a beginner like me, because:

  • There is not much to “unlearn” from JS, if you just started anyway.
  • You’re alerted when values can be of a type that would cause errors.
  • You’re forced to do something about it, basically debugging in advance.

You can read more about Rafael’s journey in the linked article

Source: Tech Recruiter Tries Coding pt. 2 — Google Apps (Type)Script

Community News, Beginner

Tech Recruiter Tries Coding — Google Apps Script

Tags:
| | 0 Comments |

Photo by Mohammad Rahmani on Unsplash

After years of working adjacent to software development, first in sales and then in recruitment, I abruptly decided to take the plunge into the other side, and start coding to automate or speed up tasks of my everyday job.

The idea came by when I started using Google Sheets more heavily, and realized how powerful it is by itself, and how much more it can become with the addition of Google Apps Script, which I discovered to my surprise is actually modern JavaScript.

Sharing this post as hopefully it’s encouragement if you are new or a beginner to Google Apps Script that with some time and effort you can begin building your custom solutions in Google Workspace. Also, if nothing else it’s all a great reminder that you can quickly enhance your UI with Unicode characters:

Source: Tech Recruiter Tries Coding — Google Apps Script