Google Apps Script supports [Triggers][1], that pass [Events][2] to trigger functions. Unfortunately, the development environment will let you test functions with no parameter passing, so you cannot simulate an event that way. If you try, you get an error like:

&gt; ReferenceError: &#39;e&#39; is not defined.

Or

&gt;TypeError: Cannot read property \*...* from undefined

(where `e` is undefined)

One could treat the event like an optional parameter, and insert a default value into the trigger function using any of the techniques from https://stackoverflow.com/questions/148901/is-there-a-better-way-to-do-optional-function-parameters-in-javascript. But that introduces a risk that a lazy programmer (hands up if that&#39;s you!) will leave that code behind, with unintended side effects.

Surely there are better ways?


  [1]: https://developers.google.com/apps-script/understanding_triggers
  [2]: https://developers.google.com/apps-script/understanding_events

How can I test a trigger function in GAS?

I have an Apps Script that constantly gets this error. Judge by the notification email, it looks like the time limit is 5 minutes. Does anyone know if there is a way to extend this time limit? Or perhaps is there a way to call the Apps Script again and it pick up from where it left off? Any help is appreciated.


Exceeded maximum execution time in Google Apps Script

I am adding a unique ID to each submission of the form. Right now, I made that ID the first field, and pre-filled it with the ID, along with a help text asking the user not to modify this field. Is there any option by which I can not show this option to the user?

Hidden field in a Google Form

How to create a new sheet in a Google Sheets with Google Apps Script?

I know it seems obvious but I just want to create a new sheet with a specific name.

Create a new sheet in a Google Sheets with Google Apps Script

I&#39;d love to be able to collapse and expand sections in my Google Docs document, making large documents easier to navigate. I was wondering if this is possible with Google Apps Script, or if I&#39;d be wasting my time trying to find a way.

Collapsing Elements in a Google Doc With Google Apps Script - Is This Possible?

I&#39;m trying to get a sense of the viability of replacing some of my Microsoft Excel spreadsheets with Google Doc Spreadsheets.  How can I create a custom keyboard shortcut to a google app script function in a google docs spreadsheet?  This is something I commonly do with VBA and Excel.

How to create custom keyboard shortcuts for google app script functions?

I&#39;m trying to get the following formula to work:

    function setDataValid(range, sourceRange) {
      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
      range.setDataValidation(rule);
    }
    
    function onEdit() {
      var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
      var aColumn = aCell.getColumn();
      
      if (aColumn == 2 &amp;&amp; SpreadsheetApp.getActiveSheet().getName() == &#39;Local&#39; ) {
        var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
        var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
        setDataValid(range, sourceRange)
      }
    }

When debugging onEdit() it shows that sourceRange in setDataValid(range, sourceRange) is null. As the range is in my sheet &#39;Local&#39; I&#39;m trying to change the getActiveSpreadsheet() to a get spreadsheet by name. Anyone who can help ?

Get Sheet By Name

I&#39;m trying to get set up with unit tests for google app scripts, and I found two projects:

https://code.google.com/p/gas-unit/
https://code.google.com/p/gasunit/

So I&#39;m confused which to use :-)

I just had a go with the unhyphenated gasunit, which seems to expect that the script is embedded in a spreadsheet, which I am a little unclear on how to do ... and the scripts I want to test are web based scripts rather than spreadsheet ones

I had more luck testing the hyphenated gas-unit, which managed to send me both an email output of the test and generate a results page in my google site:

https://sites.google.com/site/testappscript2/TestResults

so I&#39;m going to with gas-unit for the moment, but I&#39;d really like to see some official testing framework incorporated by Google.  In particular I&#39;d like to find some way to get these scripts to be run with some frequency to send me the results.  Also I&#39;d love to get some BDD going; see my other posts:

https://stackoverflow.com/questions/15665106/how-to-get-cucumber-capybara-mechanize-to-work-against-external-non-rails-site
https://stackoverflow.com/questions/15681615/how-to-use-capybara-has-text

Come on Google, you famously have &quot;Testing Rocks, Debugging Sucks&quot; in all your bathrooms?  How about better testing support for Google Apps Scripts?  

how to unit test google apps scripts?

In Google Sheets, you can add some scripting functionality. I&#39;m adding something for the `onEdit` event, but I can&#39;t tell if it&#39;s working. As far as I can tell, you can&#39;t debug a live event from Google Sheets, so you have to do it from the debugger, which is pointless since the event argument passed to my `onEdit()` function will always be undefined if I run it from the `Script Editor`.

So, I was trying to use the `Logger.log` method to log some data whenever the `onEdit` function gets called, but this too seems like it only works when run from the `Script Editor`. When I run it from the `Script Editor`, I can view the logs by going to `View-&gt;Logs...`

I was hoping I&#39;d be able to see the logs from when the event actually gets executed, but I can&#39;t figure it out.

How do I debug this stuff?

How to debug Google Apps Script (aka where does Logger.log log to?)

I want to set text or number in Google Sheet from script. 

I want to set `Hello` or number `9` in cell `F2`. I found this code so far:  

    SpreadsheetApp.getActiveRange().setValue(&#39;hello&#39;);

but that doesn&#39;t specify which cell.


How to get the correct range to set the value to a cell?

Is it possible to number the headings in a Google Docs/Drive document?

How to number the headings in a Google Docs/Drive document?

I&#39;ve written a custom Google Apps Script that will receive an `id` and fetch information from a web service (a price).

I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn&#39;t get updated.

How can I force it to re-run the script and update the cells (without manually going over each cell)?

Refresh data retrieved by a custom function in Google Sheet

I need to convert a Google Spreadsheet column index into its corresponding letter value, for example, given a spreadsheet:

![enter image description here][1]

I need to do this (this function obviously does not exist, it&#39;s an example):

    getColumnLetterByIndex(4);  // this should return &quot;D&quot;
    getColumnLetterByIndex(1);  // this should return &quot;A&quot;
    getColumnLetterByIndex(6);  // this should return &quot;F&quot;

Now, I don&#39;t recall exactly if the index starts from `0` or from `1`, anyway the concept should be clear.

I didn&#39;t find anything about this on gas documentation.. am I blind? Any idea?

Thank you


  [1]: http://i.stack.imgur.com/DyaPs.png

Convert column index into corresponding column letter

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

Something like:

    =LAST(G2:G9999)

except that `LAST` isn&#39;t a function.

Selecting the last value of a column

I want to union ranges from any Google spreadsheets.

## The example
*Sheet1!A:A*

    {12, 131, 45}

*Sheet2!A:A*

    {12, 131, 46}

*The unknown function*

    =formula_for_union_range(Sheet1!A:A; Sheet2!:A:A)

*should return*

    {12, 131, 45, 12, 131, 46}

## The question
How is it possible?

How to union ranges in google spreadsheets

I am very new to programming (have taken some of the JS courses on Codecademy). I am trying to create a simple script to determine, if given a spreadsheet with results from a poker game, who should pay whom. I opened up Google Apps Script, and wrote the following to get started:

    function addplayerstoArray(numplayers) {
      
      var playerArray = [];
      
      for (i=0; i&lt;numplayers; i++) {
        playerArray.push(i);
      }
    }  
    
    addplayerstoArray(7);
    
    console.log(playerArray[3])

The idea is to create an array with the total number of players in it. When running the code, I thought it would print &quot;3&quot; to the console. But nothing happened. It said 

&gt; &quot;ReferenceError: &quot;console&quot; is not defined.&quot; 

A) What do I not understand about how the Google Apps Script console works with respect to printing so that I can see if my code is accomplishing what I&#39;d like?

B) Is it a problem with the code?


Printing to the console in Google Apps Script?

I have a sheet with data in cols `A` through `H`.

I need to determine the last row in column `A` that contains data (it&#39;s all contiguous - no gaps in the data/rows).

There is also data in the other columns that have *more* rows of data than column `A`, so I need to isolate only column `A`. (And/or just a range within col `A`). 

I can do this on the spreadsheet level using 

    =COUNTA(A2:A100)

However in all of my researching for a Google Apps Script solution, all I seem to find are requirements to perform multiple functions encompassing dozens of lines of code - including plenty of `i++` stuff... Which I could do less complexly via offsetting directly from `A1`.

Is there possibly a column-specific way of modifying this method?

    var aLast = ss.getDataRange().getNumRows();

If a convoluted process is what is required, then so be it. But I find it difficult to imagine (and even more difficult to find!) a simpler solution.

Does anyone care to enlighten me (or pop my bubble)?

Determining the last row in a single column

I have a sheet with a column of unix epoch times (in seconds): 1500598288

How can I convert these into normal dates?

Convert Unix Epoch Time to Date in Google Sheets

I&#39;m looking for a way to &#39;pre fill&#39; a google form with specific data from a google spreadsheet. The form will have the same &#39;standard&#39; questions for everyone, but the data in the first two question will be &#39;prefilled&#39; with unique data from an existing google spreadsheet. The data will be unique based on their email address in the existing spreadsheet.

**SOURCE SPREADSHEET EXAMPLE**

&lt;!-- language: lang-none --&gt;

    Col 1       Col 2        Col 3 
    email       name         birthday  
    @mike       Mike Jones   May 9th 1975  
    @jim        Jim Smith    April 19th 1985

---

**FORM EXAMPLE ONE**

Question 1 - prefilled with data **(Mike Jones)** from a google spreadsheet.  

Question 2 - prefilled with data **(May 9th 1975)** from a google spreadsheet.  

Question 3 - blank (awaiting user response)

Question 4 - blank (awaiting user response)


---
**FORM EXAMPLE TWO**

Question 1 - prefilled with data **(Jim Smith)** from a google spreadsheet.  

Question 2 - prefilled with data **(April 19th 1985)** from a google spreadsheet.  

Question 3 - blank (awaiting user response)

Question 4 - blank (awaiting user response)

---
Does anyone know if this can be done? If yes, any help or direction will be GREATLY appreciated.

Thank you in advance!  
Todd


Is it possible to &#39;prefill&#39; a google form using data from a google spreadsheet?

I am confused about the best way to manage shared development of Google App Scripts. The thing is that editing google app scripts is done through an online editor with its own version control in place, but that&#39;s not shared with other developers as far as I can tell.  

So is the suggested practice that we copy and paste from that editing space into local hard files and then push into the version control system of our choice, and then when we want to test things we copy and paste things back into the online editor?  Seems messy and error prone.

I see some people are putting their Google App Scripts in places like Github:

https://github.com/peterneubauer/neo4j-google-apps-script

Would be great it we could push directly from git to deploy on Google App Scripts the same way we do with Heroku.  Would really like to be able to run a battery of unit tests against my App Scripts ...

Should Google App Scripts be stored in version control like GitHub

I&#39;d like to add buttons to specific cells in Google docs spreadsheet.  The [apps script UI documentation][1]  talks about how to add a new panel, but it&#39;s not clear how UI in that panel could be attached to specific rows or cells.

Is it possible to add UI to particular cells, or are we limited to adding new panels?


  [1]: http://code.google.com/googleapps/appsscript/guide_user_interfaces.html#ViewUIElements

How do you add UI inside cells in a google spreadsheet using app script?

How do I add a formula like:

    =SUM(A1:A17)

to a range of fields using the Google Apps Script API for Google Sheets?

How do I add formulas to Google Sheets using Google Apps Script?

I have a range of cells selected in a Google Sheets (activerange). I want to iterate over every cell in that range, and add a string to the end. The string is always the same, and can be hard coded into the function.

It seems like a really simple thing, but I&#39;ve been messing with the code for an hour now and can&#39;t get anything useful to happen, and the docs are really not helping. 

Here&#39;s what I have now. I don&#39;t code JS (I do know VBA, for all that helps..).


    function appendString() {
      var range = SpreadsheetApp.getActiveSheet().getActiveRange();
      for (var i = 0; i &lt; range.length; i++) {
        var currentValue = range[i].getValue();
        var withString = currentValue + &quot; string&quot;;
        range[i].setValue(withString);
      }
    }


Iterate over range, append string to each

https://developers.google.com/apps-script/guides/rest/api#parameter_and_return_types

When using the Google Execution Api for Java, the ID of the spreadsheet isn&#39;t very easy to understand. Here is the code snippet:

    // Initialize parameters for that function.
    String sheetId = &quot;&lt;ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE&gt;&quot;;
    List&lt;Object&gt; params = new ArrayList&lt;Object&gt;();
    params.add(sheetId);

Any Ideas on how to locate this. I run the Sheets api to return the id of a SpreadSheet but it returns a link, and when I use the https://spreadsheets.google.com/feeds/spreadsheets/*STRING* or just the *STRING* itself I still get:
&quot;code&quot; : 404,
  &quot;errors&quot; : [ {
    &quot;domain&quot; : &quot;global&quot;,
    &quot;message&quot; : &quot;Requested entity was not found.&quot;,
    &quot;reason&quot; : &quot;notFound&quot;



How to do I locate a Google SpreadSheet ID?

I need to create a Google App Script to respond to machine generated emails, except those also sent to one colleague or contain a particular topic outside my responsibility. 

I&#39;ve been trying to use the `GmailApp.search` API which includes all mail from user X but NOT those emails which are also addressed to user Y NOR those containing &quot;junk_term&quot;. I want the emails archived and to skip the inbox.

I created a query like so:

     &quot;from:(user_X@address.com) -{user_Y@address.com OR junk_term}&quot;

But it appears not to return any results.

How to use &quot;not&quot; in Gmail filters?

I&#39;ve made a script that every few hours adds a new row to a Google Apps spreadsheet.

This is the function I&#39;ve made to find the first empty row:

&lt;!-- language: lang-js --&gt;

    function getFirstEmptyRow() {
      var spr = SpreadsheetApp.getActiveSpreadsheet();
      var cell = spr.getRange(&#39;a1&#39;);
      var ct = 0;
      while ( cell.offset(ct, 0).getValue() != &quot;&quot; ) {
        ct++;
      }
      return (ct);
    }

It works fine, but when reaching about 100 rows, it gets really slow, even ten seconds.
I&#39;m worried that when reaching thousands of rows, it will be too slow, maybe going in timeout or worse.
Is there a better way?

Faster way to find the first empty row in a Google Sheet column

I see most examples from Google is they use only functions in a single giant script. 

e.g. https://developers.google.com/apps-script/quickstart/macros

But in our style, we usually write all functions under a single namespace, such as


    MyCompany = (MyCompany || {});
    MyCompany.init = function () {
        Logger.log(&#39;init&#39;);  
    };
    
    function onOpen() {
        var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        var menus = [{
            name: &quot;Init&quot;,
            functionName: MyCompany.init
        }];
        spreadsheet.addMenu(&quot;Test&quot;, menus);
    };

However, when I run the code above, it return

    &quot;MyCompany is not defined.&quot;


How to solve?


How to define global variable in Google Apps Script

I have a Google spreadsheet where I keep a list of bugs  and whenever I fix a bug I change the status from &quot;Not Started&quot; to &quot;Complete&quot;. I want to write a script for the Google Docs spreadsheet such that whenever I change the status to &quot;Complete&quot; the entire row gets highlighted in a certain color.

I already know that Google spreadsheet already has &quot;change color on text&quot; but that function only changes the color of the cell and does not change the color of the entire row.





All Google Apps-Script Solutions on Gang of Coders

Total of 28 Google Apps-Script Solutions