Search a try/catch error message for keywords to determine the error message to log using JavaScript match.
Filter an error message in a try/catch
The following Google Apps Script is designed to exercise how you might go about searching the error message in a ‘try/catch’ for keywords. I wanted this specifically for a tool I built that contains a lot of code (and hence a lot of potential error messages) between a try/catch, for which a very small number of people were experiencing a timezone issue with their Google Sheet file.
The aim was to use a JavaScript ‘match’ to find the keyword timezone and display a set of instructions for the user to resolve the issue themselves instead of just a generic error message.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.
When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used. But, there might be a case that the available scopes are limited. In this post, I would like to introduce a workaround for checking the existence of file ID in Google Drive without both the access token and API key.
This workaround could have been a nice addition to a recent project I was working on to audit a bunch of Google Drive file IDs. As noted in this post a big benefit of the approach is there is no need to include Google Drive authentication scopes to your project. See the source post for the code and explanation.
Loop through CSV files in Google Drive and extract their contents into specific areas in Google Sheet files.
CSV File template to copy data into
The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Use the Qualtrics API to connect with a Survey and get its details.
Qualtrics API Survey details
Most of the complexity with this simply comes in getting the correct syntax for connecting to the Qualtrics API. This Get Survey Qualtrics webpage provides a little bit more technical detail for what exactly is sent/received.
You will need to complete the 3 pieces of information at the top of the Apps Script code for your own setup:
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Use Google Apps Script to Autofill a formula down rows in a Google Sheet.
Autofill Google Sheet Formula
The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.
Handy little code pattern for clearing ranges across multiple tabs in Google Sheets. Snippets are provided for both SpreadsheetApp and the advanced Sheets service.
The following Google Apps Script is designed to reposition 2 columns within a Google Sheet. It is a small and simple bit of code but it was something new to me when I was collating lots of files together and appending columns at the end – which then needed to be moved.
In this example I am moving columns ‘collegename’ (E) and ‘shoesize’ (F) to the left of ‘postcode’ (D). So that ‘postcode’ will be the final column (F).
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Remove a users edit access to a Google Drive file via an Apps Script Web App.
Web App code sample
The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you only want you script to run every three months … another option is to manage triggers programmatically which allows you to specify the date a function should be run again.
I’ve recently been revisiting some of my old Google Apps Script posts to do a bit of housekeeping. I thought this was a nice little snippet should you want to schedule a function to run on a time-driven trigger greater than one month. The trick used is to recursively create a time-driven trigger when the function is called. The solution comes with some caveats :)
