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.
In this article Jahswill Essien describes how to use Google Sheets with Flutter via Google Apps Script.
This article assumes the following:
You have experience using the basics of Flutter and can call APIs and read an existing codebase
You have little to no understanding of Google Apps Script
You have some experience using Google Sheets or spreadsheets in general
I’m sure many of you reading this post will already be familiar with Google Apps Script and have little/no experience deploying Flutter apps but I also know a lot of Google Apps Script developers are keen to try new stuff.
If deploying mobile apps is a thing that interests you I’d highly recommend also looking at Google no/low code development platform, AppSheet, which enables you to quickly create/deploy apps often using Google Sheets as a backend. If, however, you need custom graphics or pixel perfect UI, solutions like Flutter would be my personal starting point. Enjoy!
Google Drive alone can handle small file management jobs, but for larger batches of files, it can be too much for a simple Drive script to manage. With Google Apps Script, even large batches can be executed within 6 minutes, offering businesses the monetary and time benefits of efficient file management. This report looks at how Google Apps Script improves file management with batch requests, judging its efficacy by measuring the benchmark.
We’ve regularly highlighted work from Kanshi Tanaike in Pulse and it’s nice to see it also being highlighted in the official Google Cloud Blog. I’m sure many Google Workspace developers, like me, have encountered issues with managing large volumes of Google Drive files. In the post Kanshi Tanaike highlights how batch methods can be used to greatly speed up the process when interacting with the Google Drive API.
Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script … This article will cover how to copy very large files using Tank to stream and Drv to upload and download partial content. The Apps Script Drive services have a limit on the size of files you can write, and very large memory usage can potentially cause Apps Script to fall over mysteriously
Clever stuff as always from Bruce Mcpherson, this time looking at how you can handle large files with Google Apps Script. We’ve featured some of Bruce’s other SuperFetch posts on Pulse, but developers can benefit from exploring the entire series so far on Bruce’s website. As well as SuperFetch plugins for Firebase and Twitter I’m personally interested in the evolution of the Google Drive client (Drv).
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.
This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.
At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved
Incredibly useful report and workaround from Kanshi Tanaike for Google Workspace Devs needing to get some ‘smart chips’ values from Google Docs. Hopefully classes/methods will be added to Apps Script and the Google Docs API (here is a related feature request you can star in the issue tracker), particularly as the current solution is to convert the Google Doc to .docx and then back to Google Doc.
Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.
If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”
The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.
I’ve written a few articles about JavaScript proxying on here, and I’m a big fan. I also use a lot of APIs, and it can be time consuming to keep on checking the REST documentation for how to call them and deal with the UrlFetch responses. SuperFetch is a proxy for UrlFetchApp to help.
Bruce Mcpherson has been busy again and this latest post introduces ‘SuperFetch’ the new Google Apps Script library which works as a proxy for UrlFetchApp. SuperFetch has some useful additional functionality including: built in caching and compression, standard response format and error handling, and built-in JSON parsing.
SuperFetch also has some useful features often required when using third party APIs including delaying between requests and rate limiting. Bruce has promised some additional posts detailing more complex API configuration options including authentication.
One the powerful features of Google Apps Script is with a couple of lines of code you can quickly start automating and organising your inbox. For people just getting started with Apps Script Google provide a introductory codelab ‘Accessing Google Sheets, Maps, and Gmail in 4 lines of code!’ and for those wanting to do a bit more the Google Workspace Developer documentation includes a ‘create a mail merge‘ solution.
Mail merge solutions are bit or a reoccurring theme in the world of Google Apps Script. In 2011 the official G Suite Developers Blog featured 4 ways to do Mail Merge using Google Apps Script with community contributions from James Ferreira, Steve Webster and Romain Vialard. The post references Romain’s ‘Yet another Mail Merge’ script, which he went on to develop as a very successful YAMM add-on before refocusing on the Mergo Mail Merge. Even further back when Google Apps Script was officially launched in 2009 this included a introductory video with a mail merge example.
A well as Google official channels the ability to automate your Gmail inbox has regularly caught the attention of the wider tech press. In 2013, Computerworld highlighted Jonathan Kim’s ‘Gmail No Response’ script which goes through your inbox and finds recent emails where you were the last respondent. Jonathan’s blog post is no longer available but the Gmail No Response’ script is on GitHub where it has been forked 100 times.
runs through the emails in your inbox and checks your outgoing messages for a question mark. Once it finds these emails it checks to see if they are in a date range and then sees if you have had a response. If you have not had a reply to your email containing a “?” then it adds the label “No Response”. You can then quickly see all of the threads for which you are awaiting a reply.
If you are interested in more Gmail script solutions then I highly recommend you have a look at content shared by Amit Agarwal. This doesn’t just include Apps Script solutions but also a number of Gmail tips and tricks. As many of these feature the way you can search your Gmail inbox they can also be used with GmailApp.search() or if using the Gmail Advanced Service Gmail.Users.Messages.list. A nice example of where a Gmail user tip can be used in Apps Script is Amit’s post which includes how to Search Emails by Specific Time in Gmail.
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.
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.
