AppsScriptPulse

Solutions, Admin SDK, Sheets, Intermediate

Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Tags:
| | 0 Comments |

In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings. Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. In this post I show you a pattern for building script to keep gam reports fresh.

Source: Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Library, Solutions, AppSheet, Intermediate, No Code

Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

Tags:
| | 0 Comments |

AppSheet is headlined as a no-code platform but this doesn’t mean for developers there aren’t coding opportunities. In this post I’ll be sharing AppSheetApp, a Google Apps Script helper library we’ve made at CTS which makes it easy to use the AppSheet API in your own AppSheet apps. As part of this I’ll highlight how this library has made our AppSheet powered Grab n Go Chromebook loaner solution immediately scalable and data source agnostic.

In this post the worlds of AppSheet and Apps Script collide, the resulting fusion is a library which makes it easier to integrate external data and events into your AppSheet app. This solution hopefully makes it a lot easier for developers and low coders to do more with AppSheet creating the possibility to use one (or a couple) of lines of code to interact with your app and not worry which data source your app is using. This post has more details of how you can get started and an example of the impact its already making with an alternative Grab n Go Chromebook loaner solution I’ve been part of at CTS.

Source: Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

Community News, AppSheet, No Code

Register for an online AppSheet ‘no-code’ along with Tim McLardy and Martin Hawksey 3-5 May, 2023

Tags:
| | 0 Comments |

Did you know? We’re offering 20% off registrations for our FIRST public AppSheet no-code-along! ⭐️

With AppSheet, you can simplify business processes without ever needing to write a single line of code. This can help you out if you have users managing data on mobile devices, getting lost in spreadsheets, or even still using pen and paper!

Our two part course will include hands-on experience of building a simple app alongside our in-house expert Martin Hawksey as well as covering all the basics you need to get your creative juices flowing!

Don’t sleep on it, this offer is only available until the end of March. Sign up today to secure your discount

If you are still unsure where to start with Google’s no/low code platform, AppSheet, on 3 + 5 May I’ll be joining Tim McLardy to help you get started on your journey as part of our two part ‘no-code along’. There is a discount if you register in March and Tim and I will be working extra hard to make sure you get the most out of the session.

Source: AppSheet No-Code-Along

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

Snippet, Beginner, Sheets

Rearranging column order in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.

Following on from the recent example from Scott Donald on How to sort tabs in Google Sheets with Google Apps Script, here is a handy little snippet from Kanshi Tanaike for changing the column order in Google Sheets using Apps Script and the moveColumns method.

Source: Rearranging Columns on Google Spreadsheet using Google Apps Script

Solutions, Drive, V8, Advanced

Efficiently exporting mulitple Google Docs files in PDF format with batch requests and Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.

As a reference sample situation, in order to export 100 Google Document files as PDF files, when I tested this sample script, the processing time was about 150 seconds and no error occurred. And, I confirmed that 100 valid PDF files were created in my Google Drive.

Kanshi Tanaike has been busy again, this time looking at how you can handle batch exports from Google Docs, Sheets and Slides using Google Apps Script. There is quite a bit of engineering to get your head around but if you are looking for a copy/paste solution everything is well commented for you to drop this code into your own project. If you’d like more context about the solution there is a related post on Stack Overflow.

Source: Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script

Solutions, Sheets, Beginner

How to sort tabs in Google Sheets with Google Apps Script

Tags:
| | 1 Comment |

 

Learn how to sort Google Sheet tabs in ascending and descending order with Google Apps Script Magic. Code and video inside!

Latest from Scott Donald, this time Scott is looking at Google Sheets tab sorting with Google Apps Script. Hepefully something for everyone in this post and for me it was learning about JavaScript’s Intl.Collator(), which allows language sensitive string comparisons.

Source: How to Sort Tabs in Google Sheets with Google Apps Script – Yagisanatode

Solutions, AppSheet, Drive, Sheets, Beginner

🎥 🏉 AppSheet + Apps Script for sports videography highlights tracking

Tags:
| | 0 Comments |

Image credit: Zack Akil

During the summer I was asked to help with some video recording for a rugby tournament, but rather than spending hours rewatching footage to find highlights, I quickly threw together some tech to make things a lot easier.

A slick solution that uses a AppSheet app for data collection with Apps Script to cue up video files hosted on Google Drive. The Apps Script code is hosted on GitHub if you’d like to take a peak. Hopefully this example gives you inspiration for your own little projects.

Source: 🎥 🏉 AppSheet + AppScript for sports videography highlights tracking

Library, V8, Drive, Intermediate

FilesApp is a GAS library for retrieving file and folder list in Google Drive

Tags:
| | 0 Comments |

When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

Solutions, Docs, Beginner

Check external links in Google Docs with Google Apps Script widget

Tags:
| | 0 Comments |

Image credit: Mykyta Khmel

Google Docs is a popular document editor for creating and editing text documents, spreadsheets, and presentations. With the use of Google Apps Script, a scripting language for automating tasks in Google Apps, users can enhance their document editing experience by adding custom functionality to Google Docs. One such functionality is the ability to check URLs within a Google Document.

Nice little project that has some useful code snippets/functionality you might find useful in your own projects which extracts and tests hyperlinks included in a Google Doc. The post provides an outline of how the script is setup and lets the user interact via a sidebar. I always find interacting with Google Docs a little daunting, particular parsing the document structure. The post links to a useful gist by Mogsdad (David Bingham) with some basic Google Document utility scripts.

Source: Check link statuses in Google Docs with Apps Script widget