In this session learn about combining APIs, automation, configuration as code, and more to improve your productivity using Google Workspace.
If you, or someone you know, is lucky to be at Google Cloud Next ’23 on Day 1 and would like a turbocharge to your Apps Script journey head down to the Moscone South, Lower Level and towards the Innovators Hive Theater for 11am as you’ll be in for a treat as Kara Ireland and Charles Maxson will let you know “How to get the most out of Google Workspace with Apps Script”.
I was part of a similar session at Next ’18 and it was great to meet in person new and established Apps Script users, many of them I’ still in touch with today. If you end up going say ‘hi’ to Kara and Charles from me.
Next ’23 is our global exhibition of inspiration, innovation, and education. It’s where decision makers, developers, and anyone passionate about an accessible, scalable, socially responsible cloud come together to share challenges, solutions, 10x ideas, and game-changing technologies.
Google Cloud Next ’23 is just around the corner and if you are attending in person the Google Workspace Developer Relations team will be there in force giving various talk as well having a booth at the Innovator Hive. Chanel Greco has also put the call out to:
Bring your biggest smile for the community picture at our booth at the Innovators Hive:
📷 Day 1: 1PM
📸 Day 2: 11AM
😁 Day 3: 1PM
The eagerly awaited project history capability for Google Apps Script in now rolling out. The feature is similar to the version history functionality found in other Google Workspace editors like Google Docs. As well as being able to see differences between the current and previous versions you can restore your script project to that point. Unlike Docs, Sheets and Slides there is currently no ability to ‘make a copy’ or ‘name a version’. My experience when using the Drive revisions data is that Google may merge revisions to save storage space.
To bring the power of AppSheet to more users, AppSheet Core licenses will now be included for the following Google Workspace editions…
By including AppSheet Core licenses in more Google Workspace editions, the power of AppSheet is accessible to more users. Further, Admins will have the security features they need to ensure their users are using AppSheet appropriately in their organization.
Previously AppSheet Core licenses were included with Workspace Enterprise Plus, Enterprise Essentials Plus, and Education Plus users. Google have recently announced that AppSheet Core licenses will also be included in the following Google Workspace editions:
Business Starter, Standard, and Plus
Enterprise Starter and Standard
Frontline Starter and Standard
Non-profits
Education Standard
This is great news for Google Workspace users as AppSheet includes a number of out-of-the box features which already integrate into other Workspace products, like dynamic emails, Google Chat and Google Apps Script integration.
Along with the expansion of Workspace editions getting AppSheet Core, is the news that Admins will have basic security controls which can disable external usage of AppSheet apps.
Looking at the associated new security settings support page, it was interesting to see that turning on AppSheet Core security will disable ‘external integration through the app API’:
Finally a little plug. If you are interested in AppSheet and would like to find out about training and support services get in touch. At CTS, where I work, we’ve a growing reputation in both products built using AppSheet as well as helping you get the most from the platform in your organisation.
We’re happy to announce that AppSheet-powered no-code chat apps are now available in preview for Workspace customers in AppSheet!
You can access this feature right now if you are in the Preview Program, and you can provide any feedback in this thread. If needed, additional information can be found in our help center articles. We’re targeting a full launch for later this year.
I’ve been spending a fair bit of time in Google’s ‘no-code’ platform AppSheet. Even for developers there is plenty to get your teeth into with features like Apps Script integration. One of the incredibly powerful core features of AppSheet is the ability to send dynamic emails which include forms designed in AppSheet. These allow users to interact with your app without leaving their inbox.
Whilst the inbox dominates in many business sectors, support for frontline workers increasingly leans on other communication channels including Google Chat. With the announcement of AppSheet powered no-code Google Chat apps there is now an opportunity to also push dynamic forms, send data and interact with Chat users in that space. For developers there is also the opportunity to do even more with the Apps Script integration. Follow the link to the announcement post to find out more…
Busy old couple of weeks at CTS and it’s been nice to get back to some face-to-face events. Last week I had the pleasure of joining the Google Workspace DevRel team on stage at the Google Workspace Developer Summit, London co-presenting with some guy called Charles Maxson.
A couple of years ago as part of Totally Unscripted we did a ‘State of Script‘ episode with then Apps Script PM Keith Einstein. This latest clip from the Google Workspace Developer YouTube channel captures not just the state of script, but the wider state of Workspace Dev.
The clip is worth a watch to get a summary of some of the new features coming to Workspace development. For me, not only is there plenty to get excited about my overall feeling from the Summit was Workspace development is now a first class citizen in the Google Cloud family. The state of Workspace development is good!
Over the past year, we’ve added more functionality to AppSheet, extending how it can maximize the power of Google Workspace through integrations with products such as Gmail, Google Drive and Apps Script. To improve the experience for app creators and users, we’re excited to introduce in public preview AppSheet databases, a built-in database for citizen developers to easily and securely manage their data.
During public preview, access to AppSheet databases will be enabled by default for everyone but it will not affect existing apps. Use of this feature in public preview will be included at no additional cost in your AppSheet subscription plan, but limited to 10K rows per table, 20 tables per database and 20 databases per user. Please note that these limits may change when the feature is generally available.
For people who tuned in to TU3.5: New workflow solutions with Tables and Google Apps Script back in 2020 AppSheet databases will look very familiar. Since that episode the Area 120 team behind the Tables beta moved into Google Cloud and specifically AppSheet.
The big question for me is whether AppSheet databases can find a sweet spot for citizen developers looking for something with more performance than a Google Sheet and easier to setup than a Google Cloud project. In public preview AppSheet databases are limited to 10,000, which interestingly is the same limit as the old paid version of Tables.
Ultimately this may however be less able size and performance but a data solution that the AppSheet team has full control, particularly removing a reliance on features developed by the Google Sheets team.
Google Workspace is the most popular productivity tool on the planet — relied on by more than 3 billion users. To help organizations thrive in a hybrid world, we’ve invested heavily in immersive connections, our approach to bringing people closer together through our communication products, and smart canvas, our next-generation collaboration experience, while enhancing our cloud-first security model to help people work safer.
Today is the first day of Google Cloud Next ’22 and this flagship event is being used to announce a number of upcoming features for Google Cloud and Workspace. This post (source link at the end) from the Google Cloud blog gives a summary of some of the key announcements including:
Bringing people together with immersive connections – which announces a number of new features being introduced to enhance Google Meet, the much requested feature to include inline threaded conversations in Google Chat as well as broadcast only Chat spaces
Collapsing the boundaries between people and apps with smart canvas – which highlights updates, mainly to Google Docs, to enhance productivity with custom building block and user defined variables. Google Sheets is also getting some smart chips enhancements including smart chips data extraction and third party integration as well as a new timeline view rolling out this month
Work safer with Google – a reiteration of Google’s commitment to security with updates on data loss prevention being extended to Google Chat, Trust rules in Drive and client side encryption for Gmail and Calendar
Extending the power of Workspace – the one perhaps of most interest to Pulse readers are the opportunities to build on Google Workspace with additional APIs for Meet and Chat, a Meet add-on SDK to third party integrations directly in Meet, and an official Google Chat integration from AppSheet apps.
For some of these announcements you’ll have to wait until 2023 at the earliest to get your hands on them. For a wider narrative and to see how the tech press is responding to today’s Google Cloud Next ’22 announcements here are some stories that have caught my eye:
Build an automated system that will be automatically deployed to one of the destination spreadsheets when the code is committed to the GitHub repository.
Managing code particularly in container bound projects can be a real headache for Apps Script developers. The Script REST API has made this a lot easier both is terms of managing scripts but also opening up options for different development environments, including local development using clasp and your preferred IDE.
In terms of version control there are a number of solutions Apps Script developers can now consider. The Google Apps Script GitHub Assistant Chrome Extension is a popular option as it extends the existing online Script Editor with integration with GitHub and several other Source Code Management services (GitHub Enterprise/Bitbucket/GitLab).
For developers interested in developing projects locally there is perhaps even more choice. One solution we’ve featured a couple of times in Pulse is the use of GitHub Actions:
GitHub Actions makes it easy to automate all your software workflows, now with world-class CI/CD. Build, test, and deploy your code right from GitHub. Make code reviews, branch management, and issue triaging work the way you want.
The latest example for using GitHub Actions comes from Goran Kukurin (gorankukurin.com). Goran has shared a setup for developing code in Google Sheets with development and production versions. As well as using GitHub Actions to automatically push code to the correct Google Sheet version a shell script is used to modify the custom menu name as a useful reminder so you can see what version you are testing:
You can visit Goran’s post (linked below) for an example repo and instructions on how to setup. If you are using a UNIX based operating system it should be straightforward. If like me you are on a Windows machine there are some minor tweaks and possibly big node-gyp headaches to overcome, which I’ve highlighted below (in Goran’s website post some commands with -- are displaying as - – this GitHub repo and Medium version are ok).
Windows Tips
You are better using PowerShell rather than CMD so you don’t have to modify things like $HOME to %HOMEPATH%.
If you’ve not already got node-gyp installed the setup that worked for me on Windows 11 was:
Node 14.19.3
Python 3.10
Visual Studio Build Tools 2017
After installing with npm install -g node-gyp there are Windows specific setup instructions (don’t forget npm config set msvs_version 2017).
After creating the spreadsheets I needed to specify the directory by including src (I think this is a nit) e.g.:
mv src\.clasp.json .clasp-prd.json
To encrypt your clasp credentials you might need to install install GnuPG.
If you are running the setup build tasks in VS Code and get:
'.' is not recognized as an internal or external command
I solved this by configuring npm to use bash.exe, some other options are give in this SO answer.
Final thought
There is a lot more you could do with GitHub Actions like pushing to multiple production spreadsheets and much more. This post from Goran Kukurin is a great insight to what is possible and we look forward to seeing where other Apps Script devs go with it.
This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.
Another interesting report from Kanshi Tanaike where they test the ability to get values from the Google Sheets GOOGLEFINANCE function with Google Apps Script. For context, as highlighted in the report, Google announced in 2016 that historical data from the GOOGLEFINANCE function would no longer be accessible using either Google Apps Script or the Google Sheets API.
The 2016 announcement followed news in 2014 that the Finance Service, which allowed direct access to current and historical stock data, was deprecated (if you are interested in seeing what you are missing out on here is the Internet Archive snapshot of Finance Service from 2013).
So clearly there is a trend here in Google not wanting you to programmatically access stock data with their services and I anticipate the GOOGLEFINANCE function will be updated to prevent access from Google Apps Script, but in the meantime you can have some fun :)
