Google Apps Script FAQ

What is Google Apps Script?

Google Apps Script is a cloud platform for writing and running scripts that is tightly integrated with Google Apps. Apps Script started out as "macros" for Google Sheets that had simple-to-use connectors to other Google's applications, but it evolved to be much more. Now Apps Script allow users to enhance Google Docs, Sheets and Forms with dialogs and sidebars to interact with the user in compelling ways, also allows to deploy web-apps that can be accessed on their own or be embedded in Google Sites, and some more. All using a simple scripting language based on JavaScript that is hosted and executed on Google servers, not requiring any installation or set up, dependencies or other common worries that bother developers every day, really lowering the bar for newcomers.

Do I need to be a programmer to use Google Apps Script?

No, not to use. There's lots of scripts already developed available on galleries/stores that you can easily install/copy to your Google Docs, Sheets, Sites, etc, and use right away. Most have parameters and interfaces that you can configure to tailor to your needs.

But to develop scripts you'll need to have some programming knowledge, yet it does not require a great programmer to write a functional, specific (to your needs) script. Writing public Add-ons or other widely and diverse used scripts is a harder task though.

Do I need to know Javascript to write Google Apps Script?

Definitely not, specially because only part of Javascript is "used" in Apps Script. It's only its syntax and a handful of built-in functions (e.g. to operate arrays and date objects). The great deal of DOM specific knowledge and functions are not used in Apps Script, simply because there's no DOM1.

Of course, knowing Javascript will help, but it's not that much of a leverage. I didn't knew when I started playing with Apps Script and found it super easy (today I'm an Apps Script Top Contributor!).  Javascript syntax is very flexible and lenient, and there's tons of documentation everywhere. If you already know how to develop in any language you'll get productive in no time.

Now, if you don't know how to program you'll have to put some effort into it. Programming can be very fun and rewarding. If you ever thought that you should know more about programming, Apps Script is a great platform to get start (if not the best, in my opinion). But then again, learning to program is not that easy, especially if you're not inclined to maths, analytic thinking and the like. If don't want or have the time to learn how to program right now, it's easy find to public scripts or hire developers to write something specific for you.

Where can I find free scripts or hire people to write one for me?

Apps Scripts have two official repositories for scripts, the Script Gallery (accessible via a menu in the Sheets *old* version) and the Add-ons page (accessible via a top-level menu in Google Docs and new Sheets). Also, there's lots of scripts snippets that you can paste in your files' script editor and customize with little effort, most common places to find those are in StackOverflow under google-apps-script tag, and Apps Script Google+ communities. 

About hiring, there're companies that work with Apps Script and individual freelance developers, like myself, that you should contact individually by searching their profiles and pages on the Internet. Since Apps Script is somewhat new, I don't think there's a published catalog of companies or freelancers available, you'll have to use good-old Google search to find them.

I want to write scripts to automate my tasks, where do I start?

I suggest that you start by reading Apps Script documentation page, specially the parts regarding your task (e.g. Gmail, Sheets, etc). Also, walk-through the tutorials, copy code into your scripts and run them, it should give you a better grasp of Apps Script's power and required effort.

Then, before attempting to go straight at your task, take a step back, look at your task and think if what you want to do is feasible, start with smaller parts and think of little compromises or tricks that could make your life easier (for example, move the emails you want to handle into a specific label, or all pertinent files into a separate folder), make your problem smaller at first, then try to tackle this easier task. If you have success, you'll get confident and then you can "complicate" your problem in small steps until you reach your goal.

If you ever get an error message or behavior that you don't understand, try making it simpler, copy just that specific line into a separate function and test it with a sample, simple data. If you ever get stuck, this approach will also make it easier for you to ask for help, since you'll be able to share a complete, yet small function that will allow others to reproduce and understand your problem easily. The best place to get help with programming doubts is StackOverflow, remember to tag your question with 'google-apps-script' and other pertinent tags, this allows experts and enthusiasts on those areas to find your question easier. 

Just a caveat: questions like "How do I do this?" or "Can this be done?" are usually less interesting for experts to answer, specially because it's clear that the inquirer has placed no effort into his question and just wants someone to do his "homework". A good question, that get experts excited to answer, normally are about a specific problem or error message and, more importantly, have some code in it. And if your code is short (so it's easier to read) and complete (so others can run and test themselves), your question will surely get answered in no time, with code and comments specific to your problem.

Why are my variables being lost when I run my script again?

Because it was designed this way (like it is in any other language/environment). Variables, no matter if you defined them in the global scope, lose any changes and have to be re-assigned on every run. The correct model you should have in mind is that every execution of your script (even if it's a callback from popup) is a fresh new copy of the script, and therefore have no relation with any previous run. 

Ok, but this explanation does not help you much with your new problem, which is: How do I save some variables so I can use them later?

Well, you have lots of options, like using a Sheet range, Properties Service, a Drive file, etc. In any of these approaches you're going save the desired value in a place where you can go back later and retrieve it. Here's a code snippet exemplifying this.

function helloCount(name) {

  //retrieve variable from persistent storage, in this case UserProperties

  var userProperties = PropertiesService.getUserProperties();

  var count = userProperties.getProperty('runCount');

  //check if value was there (saved)

  if( count === null ) { //if it wasn't, this is probably the first run

    count = 0; //then assign default value

  } //else ok, let's use the saved value

  //program logic

  count = count + 1;

  Browser.msgBox('Hello '+name+'! Total times you ran this: '+count);

  //save variables back to persistent storage

  userProperties.setProperty('runCount', count);

}

I'm hitting a quota limit, what can I do?

It depends a lot on what type of quota it is. But it's very likely there will be some compromise, which may be acceptable or not. Also, nearly every workaround for quotas limitations increase a lot the code complexity. Here's a list with some of Apps Script quotas.

• Service invoked too many times in a short time

Regardless of the service invoked, this is a "calls per second" (or per minute) type of limitation. Meaning you're calling it too fast in a tight loop.

If possible, the best way to fix this is to process each call to the service right after you called it, instead of processing all after your loop finishes. This might take enough time to avoid this problem, but if you can't (e.g. you don't have any relevant processing to do) then you can insert a manual delay inside your loop to make it slow enough, using: Utilities.sleep(<milliseconds>);

•  Service <blah blah blah> for one day

This quota is very similar to the above, but since now the limit is calls per day, the solution will require a compromise. Basically because you cannot work around this. It usually involves pacing your script way down and/or waiting the next day.

Usually, this kind of problem only happens when your script is being triggered automatically. If it's a time-driven trigger then it's easy, just increase the interval of your trigger, if it's running every 5 minutes, try every 10 or 30 minutes, and so on. Note that you'll still have to wait for the next day the first time, hopefully, if this setting works, it will not fail due to this quota again.

Now if it's a different type of trigger, like spreadsheet onEdit, on form submit, or if you have a web-app running as the developer (you), the solution is a lot harder. Well, to the web-app case, if you can change it to run as the user accessing, then do it, since this is a far easier solution (each user is using their own quota, instead of everybody on yours). But if you can't, or if it's the trigger situation, the solution I usually implement is to create a `task queue` and have a paced-down time-driven trigger to get a task from the queue and work on it. But this is much easier said than done, and the implementation will depend a lot on what your task is.

• Exceeded maximum execution time

A single execution of Apps script can last no longer than 6 minutes and you're hitting this limit. The workaround I use to this problem is the same as described above (please read last paragraph). But you may have an alternative that's not so difficult to implement depending on the task you have, which is to manually split it. For example, if you're processing emails, restrict your script to a label and move the emails you need to process into this label in chunks that the script can process under 6 minutes. Same idea if you're processing files on Drive or Calendar events, Spreadsheet cells, etc... restrict the scope of the script so it can work smaller parts. Then manually move small chunks of "tasks" into scope.

How do permissions work on Apps Script?

Here's the link to the official Apps Script documentation on permissions. The most common confusions are related to spreadsheets, so that's where I'll focus. But the ideas are analogous to other situations.

If your script runs from a custom menu or an image, therefore requiring the user to actively click on it, the script will run under the permission of the user clicking on the button. An authorization popup will be shown to him the first time and after granted, the script will run under his account. So, if the script sends emails, the from address will be his. If the script changes the spreadsheet, his account will show on the revision history. And if you, the developer and spreadsheet owner, protect a sheet so that this particular user cannot change it, then the script will not be able to change it either.

Now, if your script runs via a simple trigger like "onEdit" or "onOpen", it runs automatically and no authorization prompt is shown to the user. Therefore, even though the script is running due to an action of the user, it cannot access anything sensitive from this user (since he did not authorize it). So your script will not be able send emails, access other files, and so on. In this situation all you can do is work within the current file that contains the script.

There's a third mode of running scripts, which are the installable-triggers. Unlike simple triggers, these triggers require that the user manually go to script editor and set the trigger themselves (or run a script directly, like clicking on a menu and authorizing the script to set triggers on his behalf). Therefore these triggers, even though they get executed automatically (probably due to other users' actions), run under the permission of the account who have set up the trigger in the first place (or authorized the app to do it on his behalf). For example, if I set an "on edit" installable trigger on a spreadsheet and you make a change this spreadsheet, the script will run under my permission, and will be able to access my files or edit all sheets that I can, even if you don't have access to them.

My script is very slow, what is happening?

It could be various things, and some analysis to your specific code is required. But first of all, there's a natural lag involved with Apps Script, since the code runs on the server and not in your computer/browser, it's never going to be super fast due to the network round-trips. To exemplify what I'm saying, create a function with a single "Browser.msgBox('Test');" statement and run it from a spreadsheet, that's as fast as you'll get.

Now to analyze how you can enhance your script, run it directly from the script editor and then check the menu "View > Execution transcript". It shows all the calls made (to the server) and how long each one took. Note that native Javascript functions will not show there (e.g. math, dates or string operations), and you don't need to worry with those (normally), they're around a hundred times faster than any server call.

The usual tricks to make a script faster is to use less server calls, I know it sounds weird, since that's what Apps Script makes so easy to do. But the goal is to make less calls that do more. For example, if you have a for-loop that iterates through all rows in a sheet, getting each range every time (e.g. using offset or getRange), then get the info you need from it (normally getValue). You can replace all these calls (within the loop) with two simple calls before the loop that get everything you need at once, e.g. var values = sheet.getDataRange().getValues(); . Then instead of doing server calls inside the loop, you do only Javascript operations (~100x faster), and after the loop you set all data back at once (if you have to).

Most services have some sort of bulk-style methods that allows for this kind of approach, since it's a common issue with all cloud services, not just Apps Script.

Which libraries work with HtmlService?

Basically just latest JQuery and JQuery-UI. Not even Google libraries work, like visualization, maps and so on.  You may find some library small enough, written in strict Javascript that might work, but anything more complex will not.

This "non-compatibility" happens due to security restrictions enforced by Apps Script on the applications we develop. Their concern is that one might write malicious code and host it within Google apps, even though they warn the users multiple times that they're not associated or guarantee any code we write. Anyway, it's an understandable concern, that sadly impairs a lot the development in Apps Script.

How many users can access my web-app simultaneously?

It depends on a lot of things. It may fail with one user or work for a million. The key issue to this question regards the various quotas we have to live with in Apps Script. And most of them we're not aware of until we hit them.

It's a hard thing to analyze, but let's try the bare minimum. Does your app run as the user or as the developer? If it's the developer, than it's going to be much less users, mainly because everything counts against your quota (instead of each user having its own). But just to give you some reference, I'll give a wild guess based on my experience... if you're a great developer and already worked-around the various quotas, maybe a 100 users, probably less. The only way to know would be testing, but as you can imagine, this is hard to do. If you app is standalone running with access to anyone even anonymous, then you might have a reasonable shot at stress testing it with tools like JMeter. But the sole acting of stress testing your app can make it break so hard that it might be difficult to recover from. Your account may get blocked for a day or more until your quotas are refreshed, etc.

If your app runs as the user, like an add-on, then it might work of an "infinity" amount of users. And even if it breaks, it's going to be individually, since the quotas are counted against each user and not you (the developer). You just have to pay attention in your script for resources that are shared and therefore count against you. Usually app-centric storage, like ScriptDb, Cloud-Sql or your custom external backend (e.g. App Engine). If one of these goes wrong your app may fail to all your users at once!

How do I stay informed about Apps Script updates?

Here's the official Release Notes page, where you can find the "most important" changes announced by the Apps Script team.

But if you have an issue, it's usually better to star it on the Apps Script issue tracker (or report it, if it's not already there). And receive its updates via email as soon as they happen.

Lastly, if you're actively working on Apps Script, you should constantly check google-apps-script tag on StackOverflow. When there's a problem, it usually shows up first as a question there before it goes to the issue tracker. Also, answering questions and seeing others usage and ideas with Apps Script is a great way to stay sharp.

Have fun!