Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.
Bulk convert Excel files to Google Sheets
The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.
Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.
For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format), the challenge however is working out the user’s timeZone. For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].
function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}
The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.
If you’ve got other tips for handling user time zones feel free to pop them in the comments.
Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.
I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)
In the latest Google Workspace Developers Newsletter – April 2022 (if not already you can subscribe here), there is a handy summary of ‘Must see Google Workspace Platform sessions’ at Google I/O, 11-12 May 2022. Google I/O is being run as a hybrid event and anyone online can register for free and the program is worth exploring:
Join us for Google I/O 2022. Connect with experts, get inspired, and expand your knowledge. From no-code platforms to automated Chat applications, the team has put together an informative and educational set of sessions to get you up to date with the Google Workspace Platform.
Keynote
The cloud built for developers: Learn how Google Cloud and Workspace teams are building cloud services to help developers and technologists create transformative applications.
What’s new in the world of Google Chat apps: Integrate services with Google Chat, explore visual improvements to Google Chat apps, and discover new Google Chat API features.
You use Google Sheets as your own Podcast Manager that will automatically download your favorite podcasts to Google Drive and instantly sync across all your devices.
Amit Agarwal is always coming up with creative uses for Google Apps Script. His latest project is a great example of what can be achieved with a little code and a lot of know-how. Even if you aren’t a podcast fan this project is worth checking out as Amit is expert at writing concise and efficient code.
Some highlights to check out once you make a copy of the ‘Podcast Manager’ Google Sheet are use of CacheService for getting/putting a last update time and using the .filter(Boolean) trick for ignoring blank cells when using .getValues() on Google Sheets data. There is plenty more going on and worth spending some time using the Script Editor debugger and breakpoints to learn from a master.
Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.
Behind the scenes look at what went into creating an Apps Script-native Add-on to generate Webhooks for Google Sheets. ICYMI: You can access the add-on from this link and know more about what it does here
It’s worth checking out Sourabh Choraria’s latest Google Sheets add-on which enables users to quickly setup a Google Sheet to receive data from other services which support the creation of webhooks. As part of this solution the add-on makes extensive use of the Apps Script API, which can be used to programmatically manage Apps Script projects including deployments. This post from Sourabh highlights some of the key endpoints used in the Apps Script API as well as a number of lessons learned about deploying web apps for users.
One of the benefits of Google moving Apps Script to the V8 engine is the possibility to drop in existing JavaScript libraries. Max Makhrov recently highlighted on Twitter how JSONata, which can be used to query and transform JSON data can be used in Google Apps Script:
JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation. A rich complement of built in operators and functions is provided for manipulating and combining extracted data, and the results of queries can be formatted into any JSON output structure using familiar JSON object and array syntax. Coupled with the facility to create user defined functions, advanced expressions can be built to tackle any JSON query and transformation task. – JSONata
JSONata is a solution better understood by trying it out, which you can do thanks to the script project shared by Max on Twitter or on the JSONata website. If you are interested in data query/manipulation solutions for Google Apps Script you might also want to check out our previous post on using AlaSQL for Apps Script.
If you run and maintain an Open Source project you’ll typically will want to keep track of things like your downloads, stars, commits over time, etc. to help you gauge engagement and overall health of your project. Here’s a quick way hack to keep track of some of this data in Google Sheet which will help you simplify the collection of data and help you better understand what that data means for your project.
This post was picked up by the official @WorkspaceDevs Twitter account, which has been highlighting and amplifying more community contributions recently (hint: you may want to follow/tag the account :)
The two things that caught my eye in this post. First, was the pattern used to get stats from multiple GitHub repos which are all appended in the same row (repo 1 cols B-F and repo 2 cols G-H).
Image credit: Codenotary
As a bit of a REST API/auth geek the second thing that interested me was the use of the Acceptheader, used by GitHub to specify the REST API version being called in UrlFetchApp.
Periodically check for any changes in a given Google Drive file and receive an email notification if there are any, within your given time-frame.
Email notification of Drive file changes. Image credit: Phil Bainbridge
The following Google Apps Script is designed to periodically check for any changes that have occurred to a given Google Drive file (ie edits to it) and send an email to notify of that. It goes through the Revision (Version History) of the file and looks at the modification dates for those that match with your given timeframe, to then collect the Username & Email address of the person that made those edits for inclusion in the email.
So as an example you may have a Google Sheet where you want to setup a check every 4 hours, to then be emailed if there have been any edits to it, along with who made those edits.
