Random tips putting Google Apps Script to work.
Check out links for reading on our Flipboard magazine
Open a new google spreadsheet.
Pull the .gs and .html files from this repository into your sheet's attached script editor.
Enable any advanced services listed in the menu functions like sheets (both in the script editor and via link in the GCP console). See notes in the menu functions.
Refresh your sheet and use the new menu to try out various demonstrations.
Some of these will be used in our DevFlow Project
This section inpired by this original gist.
Initially intended to extend G Suite apps, I like to think of Google Apps Script as a gateway to more kinds of development. Think of it as workflow glue and the power of programming that can interract with Google Apps and external APIs too!
Provide a living document for whenever someone asks, " so, how do i get started with Google Apps Script?".
Just the orgainizing principles and some key links.
Scripts are 'bound' to a container like sheets, docs, slides or forms. These can be accessed from the containing doc and opened say in sheets by going to the menu Tools > Script editor. Scripts can also be standalone for addons or web apps. Your script home page is a dashboard found here script.google.com. The help link there will get you to an explanation of the dashboard.
Getting data into and out of a sheet using 2d arrays and few methods is the key to moving on from custom functions to macros to executing business logic on an array and then writing the results back to your sheet.
The documentation alludes to app script's basic javascript feature support which is 1.6 and some of Javascript 1.7 and 1.8.
GAS support for javascript features can be confusing to a newcomer, especially when learning about javascript from the ECMAScript standards.
The ECMAScript (ES) implementation called "V8" is the javascript engine used by apps like chrome, node.js, opera, etc. This engine supports some of the latest ES javascript conventions.
However, google apps script is running on the servers and we have yet to see when or if the Google App Script (GAS) team will support more recent ES so developers can write consistently when writing javascript whether it's a web app or inside a GAS.
Star and follow this issue: https://issuetracker.google.com/36764074
Advanced users:
You can write more advanced JS using Typescript and interect with standalone script projects from the cli using Node package clasp.
However, if you're just starting or don't know the command line, just use the online editor and understand it's older JS if you hit an syntax error. Focus on the action plan first.
Google has stated it will support modern JS in thier G.A.S. offering but will not say when. If so, there should be great performance gains too!
Start with this Overview of Google Apps Script. It includes a link to codeacademy for learning javascript if you need help there.
4 People and their books, classes, helpful websites or other activities they want to share in this context
Please add to the comments and I'll include here over time. Thanks so much.
Please see the nice add-ons introduction and the end comments; in the middle is also a demo of ultradox which i haven't used. Building G Suite add-ons with Google Apps Script (Google Cloud Next '17)
- From https://gist.github.com/oshliaer, an amazing gist full of great resources by Alexander Ivanov
- https://gist.github.com/Blueprinter gives us this site of documents for how to across a lot of categories
This great gist by Amit Agarwal
For tutorials covering the basics, check out The website of Barrie Roberts
This G+ post has lots of real uses for GAS. Highlights
- workflow automation testimonials
- Andrew Roberts provided links
- Bruce McPherson’s Apps Script website I've purchased his materials. Especially good for those moving from VBA. Lot's and Lot's.
- see his list of links to spark ideas and find good stuff
A few options for how you could go about learning more and what that path might look like.
Try using the above links to find out how to do the following:
1 Write a custom function in a container bound sheet script
2 Call that function from the sheet
3 Play with recording macros and editing the scripts they generate (in macros.gs)
4 Lookup the documentation on some of the methods you see in the scripts; For example, go to the api documentation and search for more information on the methods you see used in the macros.
Such as this macro recording me selecting a range and then simply entering the text what into E5:
/** @OnlyCurrentDoc */
function testmacro() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('E5:F12').activate();
spreadsheet.getCurrentCell().setValue('what');
};In the above, you could search the api documentation for 'SpreadsheetApp.getActive()' and learn that it "Returns the currently active spreadsheet, or null if there is none." This is an object assigned to the spreadsheet variable in your macro example above. Simarlarly you could search for 'getRange' or 'getCurrentCell()' or inside each of those 'activate()' or 'setValue' respectively. Make the connection between the api documentation and how you see the macros recording your actions. HINT: inside the script editor you can hit Ctrl + Spacebar and see a list of the services available to the script. Including the 'SpreadsheetApp' you are exploring. You can scroll or type to find the service you want and on each find hit enter followed by a period to walk the list of classes or methods for the current selection. This is effectively 'walking the API' which I find more interesting way to figure out what's available than trying to read the API documentation. Put the two together and you really start to get it.
SpreadsheetApp.getActive().getRange(a1Notation).getValues();If you look at the hints while walking the api as in the example above you can notice getRange wants a1Notation (there are others) and that .getValues returnes a [[]] 2d array - an object of rows whose rows are arrays...a spreadsheet range :)
HINT: now just try getting and setting one value vs many and you are on your way to working with scripts. As you advance, checkoout libraries to see what proplems are common enough to require libraries. Star the gists to browse and file the links.
Learn more by solving one problem at a time. Join the DevFlow Project to learn how to create a sheets addon and test your understanding. Good luck. Help each other.
These working sessions document the creation of a set of menu driven functions in a script container bound to a sheet. Watch it evolve and read through the notes. Follow along the videos and try them yourself.
-
This section is from a phase II project inside of the repo DevFlow; to catch up on that project try reading that repo's issue 46
-
Specifically, a multi-part set of working sessions. The notes are included below along with links to the unedited recorded sessions when possible.
-
GAS Basics working sessions to try out GAS on sheets data; some of these could be turned into demos of successful "GAS patterns"
- Each week for a series of weeks will cover some GAS basics and share the sheet with it's script as well as the recording, so you can follow along and explore.
- We recommend making a copy of the provided files and then working from there. If you want to make comments on the file, just add comments in the sheet we link you to.
-
Topics covered include
- Update Multiple Values
- Manipulate Disjoint Ranges
- joint discovery based on interest and time
- on open build menu calling these
- others? practice using and presenting from editor
- dates - cancelling working session on Aug 1st
-
This week's Cool links:
- Working Sheet File w Script
- How Fast is Realtime? Human Perception and Technology
- JavaScript engine fundamentals: Shapes and Inline Caches
- the original tweet about the article has a rich discussion see twitter post
- The One Tab crhome and firefox extension save 95% of memory when you have too many tabs open; use as intended
- Digo Chrome Extension extension for Diigo.com tool
-
Unassigned Action Items: research items, problems to solve, notes, special items
- Other DevFlows from GDEs we gave feedback (and got mentioned) in a google developer expert's DevFlow for an org team. This G+ post shows him listing a document for feedback and the document itself has comments you can review in comment mode. He has a great idea about including manifest files (more later). IF you are ever keen and the post is still up, check it out for a look at open collaboration about methods.
- OUR copy of doc with comments open the coments thread...feel free to comment on this one for our internal use
- ideas for GAS series working sessions
- use the "Gist query my sheet" demo to build a 2d array from sheet data
- try out a few patterns on this data
- grab real problems posed in G+ for discussion; for example this one has a recommended solution where the answer involves a simple and an installable trigger. Triggers might be a topic
- setup a series to solve, then work the list practicing working ing GAS
- Incorporate manifests
- use the "Gist query my sheet" demo to build a 2d array from sheet data
- Other DevFlows from GDEs we gave feedback (and got mentioned) in a google developer expert's DevFlow for an org team. This G+ post shows him listing a document for feedback and the document itself has comments you can review in comment mode. He has a great idea about including manifest files (more later). IF you are ever keen and the post is still up, check it out for a look at open collaboration about methods.
-
Next Week: part 2
- GAS Basics working sessions to try out GAS on sheets data; some of these could be turned into demos of successful "GAS patterns"
- UPDATED approach, use the new repo to get the code and add it to a fresh sheet.
- Topics covered include
- Review great links
- Create a new repo for this "BurningGAS"
- Pull in last weeks two functions and onOpen menu in a new repo
- modify to create the input sheets
- Gist query my sheet build
- This week's Cool links:
- our new repo for these GAS sessions
- Check the learning GIST for these two new links; we will likely pull the GIST into our new repo
- This great gist by Amit Agarwal
- For tutorials covering the basics, check out The website of Barrie Roberts
- explore these sources for data to use, especially 5
- Looking for data? Try Kaggle
- Javascript Linter Online tool
- atom plug in options
- next week's topic
- Next Week: part 3
Canceled due to holiday; merging with General Session II.
-
GAS Basics working sessions to try out GAS on sheets data; some of these could be turned into demos of successful "GAS patterns"
-
Topics covered include
- Catch up on the gas workshops via the video links and
- Demo 'Gist query my sheet' shows SQL like query to a 2d array and use of advanced sheet service to right-size the new sheet with the exact number of rows and columns required
- demonstrate GitHub /compare feature
- 1.1.0 Gist query my sheet "make it possible"
- 1.1.1 Faster - batching more actions for speed "make it faster"
- 1.1.2 Functional - slightly slower for a reason, leveraging api best practices and workarounds "make it pleasant" for the first run, re-run
-
This week's Cool links with a few more added for time off:
- the importance of speed Incredible information here.
- Designer vs. Developer YouTube series - Designer vs. Developer aims to provide takeaways, solutions to workflows, tools & discussions on everyday struggles. Especially check out the notes for links. For example, #17 "Using Gestalt Principles to Improve Your Design" mentions these two gems among others
- revered article on javascript patterns with modules
- replay of Google's App Maker panel session Totally Unscripted panel on App Maker Questions
- dotfiles Your unofficial guide to dotfiles on GitHub. Let's discuss if we need to define this as part of our setup and config or an open call for tools that do this
- A deep set of highly competent content for GAS skills This person's work is amazing. Go deeper with the links and learn lots.
- Google Apps Developer Series: How to Increase Traffic to your Add-On With AppsScript Inventor of GAS gives addon advice, originally a Cloud Connect Live Event Streamed live on Jun 2, 2015. Great material. Also, they mention which javascript engine is being used!
- Using Google Apps Script to automate G Suite (Google Cloud Next '17) This more recent walkthrough has a very good and straightforward set of code examples in their presentation. Especially look for stubbing of services for testing in the Gmail example and the use of templated HTML with regard to the use of escaped javascript in the html file in the web app section - not sure if the second one is best practice or not but it's interesting.
Try this repo where you can see some of these functions translated into sheets add-ons. DevFlow is also about workflow for professionalizing developmenet of google apps script. Here are a few links: Install and try our add-on from an unlisted link: rudimusmaximus/DevFlow#65
If you want to see more about the history of the making of the repo with lots of readme links, see rudimusmaximus/DevFlow#46
Remember to star the repo, thanks.
We will be cleaning up the documents, so look for updates over the comming months.