AppsScriptPulse

Gotchas, New IDE, Solutions, V8, Intermediate

How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit

Tags:
| | 0 Comments |

I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:

Exception: Argument too large: SQL

I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.

Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉

Source: How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit

Gotchas, New IDE, Official News

[Official] Apps Script project history – New Feature overview!

Tags:
| | 0 Comments |

We are excited to announce the general availability of project history for Apps Script! Find out how you can get started using this feature.

In Pulse we’ve previously featured the announcement that the new project history was rolling out to Google Apps Script. This feature should have finished following out and this video from the Google Workspace team covers how you can use project history in the Apps Script IDE. As well as the video there is also an update to the Google Apps Script Versions documentation page.

The key takeaway for Google Apps Script users is unlike Google Docs which can automatically save a version history of a document, Apps Script project history requires the user to use deployments (Deploy > Manage deployments) to create a version should you want to see get a highlights or code changes between the current and previous versions. This will be less of an issue if you are using Google Apps Script to  deploy web apps, Workspace Add-ons or libraries, which already use deployments, but for other situations like container bound scripts you might want to get into the habit of using the Deploy button to create a version.

Source: Apps Script project history – New Feature!

Add-on, Gotchas, Intermediate

5 steps to deploying Google Workspace Editors Add-ons to the Google Workspace Marketplace

Tags:
| | 0 Comments |

This guide is not documentation, but a condensed reminder of what you must do.

Max Makhrov has put together this very succinct guide to publishing Google Workspace Editor Add-ons to the Google Workspace Marketplace. As explained by Max the guide isn’t a step-by-step tutorial, but instead provides the key steps you should remember to do. There are some great tips in this post, particularly around what to include in the recorded screencast required by the Google oAuth verification team.

Source: 5 STEPS to Deploy Google Sheets External Editors add-on

Add-on, Gotchas, Beginner

A Google Workspace Developer’s notes on publishing a Google Workspace Add-on to the Marketplace

Tags:
| | 0 Comments |

 

This guide will walk you through creating a public Google Workspace Add-on, and launching it in the Google Workspace Marketplace for as free as possible. …

I’d never developed or published an add-on before. As I was looking into it, I realized that, while it is not super complicated, it is not readily obvious — especially for anyone just getting into add-on development. Google does have a guide on developing and publishing an add-on but it leaves a lot of unanswered questions.

So I thought I would put together what I learned in this guide — a playbook for anyone else who wants to develop their own add-on.

Following on from a recent Pulse post on How to publish to the Google Workspace Marketplace published by the Google Workspace team, here’s a developer’s take on the process from start to finish. As mentioned in the post the official support resources should be your start point, but these notes spotlight some of the nuances required to publish an add-on and the appendix includes some tips on naming your add-on and where/how to host required documentation including your add-on privacy policy.

Source: Developing a GAS Powered Google Workspace Add-on And Launching It To The Marketplace

Gotchas, Advanced, Sheets

Performance report for CacheService versus SpreadsheetApp for read/write in Google Apps Script

Tags:
| | 0 Comments |

Image credit: Ignacio Lopezosa Serrano

For read-heavy applications that don’t involve components external to Apps Script accessing the cache and don’t exceed the CacheService limits, use CacheService. For write-heavy applications or for when some external parts require access to the same cache, use Spreadsheet App.

An interesting report from Ignacio Lopezosa Serrano on the relative performance of CacheService and SpreadsheetApp for reading/writing data with Google Apps Script. Some surprising results and something I think to be kept in mind is how these tests compare to ‘real world’ conditions. As also pointed out in the post there are some  service limitations of CacheService to keep in mind particularly around storage size limits.

Source: The use of Caches in Google Apps Script

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

Gotchas, V8, Advanced

Fix Google Apps Script file order problems with Exports 

Tags:
| | 0 Comments |

How to fix Apps Script file loading order and defintion visibility problems with an Exports object.

It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.

As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.

This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.

Source: Fix Apps Script file order problems with Exports – Desktop Liberation

Community News, Gotchas, Intermediate

12 Years and 1000 pages in Office, Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

Tags:
| | 0 Comments |

I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.

I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.

Source: 12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

Add-on, Gotchas, Intermediate

Alice Keeler’s top tips for getting your Google Workspace Add-on published | Google Cloud Blog

Tags:
| | 0 Comments |

If this is your first attempt at submitting an add-on for the Google Marketplace, it—like all new experiences—can take longer than expected as you learn and get comfortable with all of the requirements. You should expect pushback from both the OAuth team and the Marketplace team, as they are on the frontline of ensuring that end users have a positive experience installing Add-ons. Taking the time to slowly go through and make sure you have each of the elements along with a willingness to update and improve your application will surely result in the successful publication of your Add-on published in the Google Marketplace.

Alice Keeler knows a thing or two about publishing Google Workspace Add-ons to the Marketplace with over 20 entries. In this post on the Google Cloud Blog Alice shares some of her top tips for surviving the publication process. This includes website essentials, tips on artwork as well as creating your verification video. Follow the source link for these tips and more.

Source: Google Cloud Workspace Add-On Customization | Google Cloud Blog

Gotchas, Solutions, V8, Intermediate

If you use clasp with Google Apps Script, you need this environment switching utility right now!

Tags:
| | 0 Comments |

When working in a team and/or with a client, you want to have multiple environments. At minimum, you probably want a dev environment (or multiple ones) in which you are working, and a test environment in which the client or your team can run acceptance tests before production. Of course, they must both be separate from the production environment. To push your code to the correct environment, you need to either update the .clasp.json file manually or keep multiple copies of your script with different .clasp.json files. Fortunately, things have just become significantly easier, as I recently built an app for this purpose called clasp-env, which is available on NPM. See the source link for details.

Source: “If You Use Clasp With Google Apps Script, You Need This Utility Right Now”