Google Apps Script (GAS) is a JavaScript cloud scripting language that lets you extend Google Apps and build web applications.

learn more… | top users | synonyms (1)

29
votes
6answers
47k views

Alternating colors for lines in Google docs spreadsheet

I have a shared Google spreadsheet where several users have already filled out parts. It would be useful to have alternately colored lines to facilitate correct placement of additional data. Is ...
23
votes
7answers
24k views

Text to columns conversion in Google Spreadsheets

How do you do a text-to-columns conversion in Google Spreadsheets? For example, I have the following data string in one cell: 5,233,6,2,6,7,2,2,6,6 I want to break it apart by the comma separator ...
17
votes
1answer
5k views

How to fix smilies in Gmail showing as “J”

Is there a way to fix smilies in received emails that looks like "J" in Gmail? Other mail clients (such as Outlook) changes ":)" to smile character in Wingdings font that is letter "J". Although, in ...
16
votes
6answers
13k views

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like: function myFunction(range) { var firstColumn = range.getColumn(); // loop over the range } A cell would reference it using: ...
13
votes
4answers
3k views

Is there any way to script a Google Docs spreadsheet?

I have a really complex spreadsheet in Google Docs, and while it works, having the ability to script it a bit (à la VBA in Microsoft Office) would make it a lot cleaner. I heard that this is doable in ...
12
votes
2answers
3k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
10
votes
3answers
25k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
8
votes
2answers
2k views

In Google Spreadsheet, finding what formulas reference a given value

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice Tools>Detective>Trace dependents and Edit>Find ...
7
votes
3answers
5k views

Google Spreadsheets: how to specify entire sheet as range?

The best workaround I have found so far is: worksheet_name!$A$1:$YY but ideally I would like to be able to simply write, e.g.: worksheet_name! So, does anyone know: is there a syntax for ...
6
votes
2answers
5k views

Pass range in Google Apps script custom function without using A1 notation

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to ...
6
votes
2answers
422 views

Custom data transpose?

I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than ...
6
votes
2answers
5k views

Colorize a cell in Google Spreadsheets based on cell data

I'd like to colorize a cell based on it's contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ...
6
votes
3answers
938 views

Sorting data in Google Spreadsheet by cell entry

I have a Google Spreadsheet that lists various events. Students have entered their name next to events that they would like to sign up for. It looks like this: Each student may sign up for more ...
6
votes
3answers
23k views

How can I automatically set “last updated” cell in row Google Docs Spreadsheets?

How can I automatically set "last updated" cell in row Google Docs Spreadsheets ? I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this ...
6
votes
2answers
10k views

How to Refresh a cell in Google Spreadsheet?

I have a column in Google Spreadsheet that calls an outside webservice using URLFetch in a Google Script. The cell uses two parameters in that cell's row. After a change occurs where those two ...
6
votes
2answers
3k views

Can I use other font weights in Google Documents?

How can I use other font weights (such as Book 300 for Open Sans) in my documents? Adding Open Sans only allows me to use Normal 400, for example.
5
votes
3answers
4k views

Cell reference with colour formatting

Is it possible to reference a cell in Google Spreadsheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference dell's value. ...
5
votes
1answer
977 views

How to export / import a Google Apps Script as a file into a new spreadsheet?

I fear this may have an obvious answer, but I've been knocking my head over this and can't find it! When creating a new spreadsheet, we can easily create a script within it. I like to call it a ...
5
votes
2answers
2k views

How to avoid “invoked too many times per second” error

I made a Google Apps Script, but often I get this error: Script invoked too many times per second in this Google user account. This is the script: function papersControl(tipo, col, prev_cell, ...
5
votes
1answer
6k views

Auto create and name new sheet in Google Spreadsheet

How can I automatically create and name new sheets in a google spreadsheet from a list of names, such as a student roll sheet? I would like each new sheet to be created when I add corresponding names ...
4
votes
1answer
2k views

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting? However, my use case requires the sorting ...
4
votes
1answer
1k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
4
votes
2answers
1k views

How can I dynamically format the diagonal cells in Google Spreadsheet?

I want all of the diagonal cells (A1, B2, C3, D4, E5, etc) of a Sheet to be formatted as Black Background, White Foreground. Is there any way to do this for a Sheet that constantly increases in size? ...
4
votes
1answer
400 views

Pull Twitter Posts into Google Spreadsheets

I would like to set-up a Google Spreadsheet that automatically pulls Twitter search results into the cells. For instance, I'd like to search for "pizza" near "baltimore" and then have any pizza ...
4
votes
1answer
189 views

GmailApp service for consumer Gmail accounts?

I successfully set up Gmail Snooze in my google apps accounts (for work) and I like it so much that I want to set it up for my personal email, too (@gmail.com). I followed all the same instructions, ...
4
votes
1answer
141 views

Custom script “invoked too many times” error

Credit: Google Spreadsheets function to find distance between cities function getDirection(city1, city2, unit) { var directions = Maps.newDirectionFinder() ...
4
votes
1answer
62 views

Save daily to my Gmail in Google Sheets

Right now what I have is this in a Google sheet for my company daily lunch program: function sendLunchPerkSummery() { var d = Date(); var file = ...
4
votes
2answers
311 views

Can I convert a MS Word document to a Google Document format using GAS?

I know how to convert a Microsoft Word document to Google Documents format manually (right click, open with Google Drive). But is there any way to do this using Google Apps Script? I have looked ...
4
votes
2answers
4k views

How do I implement a script that creates a unique ID for a Google form submission?

I need to create a unique ID upon submission of a form which will be used as a project number for tracking. The number needs to be unique but not based on rows or other changeable data because ...
4
votes
1answer
889 views

Looping through the range of cells to test the dates

I am trying to create a function to check the dates in 'Dt' sheet and return Match or Not Match. It works fine with when I display my answer in MsgBox, but when I try to do it in one of the ...
4
votes
1answer
2k views

Creating a function that runs when a Google spreadsheet is opened

I want to create a function which runs automatically when the spreadsheet is opened. This function will make the selected cell the last cell without text in a certain column. Basically, I don't want ...
3
votes
2answers
121 views

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?
3
votes
2answers
1k views

How do I automatically expand a =QUERY when receiving submissions from a Google Form?

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the 'responses sheet'. The data being added to the 'responses ...
3
votes
1answer
1k views

Sum a column except current cell

Is it possible to sum all values in a column EXCEPT the value in the current cell? That is, the cell that is to hold the result. With a normal SUM(E:E) command the result is #REF! with a Circular ...
3
votes
1answer
31 views

What can break the relationship between the onFormSubmit function and a spreadsheet that is attached to a google form?

I had a working Google form, Google spreadsheet, and GAS script which would be called when the form was submitted. It worked fine until the did (one?) of the following things: Cleared out the ...
3
votes
1answer
1k views

Why would this Google Spreadsheet onEdit script be so slow (2 secs to change cells, 3 secs to update)

I've put together a script for a spreadsheet based on what I have seen here and other sources that should provide a simple username and timestamp function. I have another spreadsheet that uses a very ...
3
votes
2answers
1k views

Iterated variable (i) for argument on range in Google Scripts API

for (i=0; i<numItems; i++) { var cell = sheet.getRange(12,i); cell.setFormula(i); } This simple formula should go to every place on the column 12 where there is data, and replace it ...
3
votes
2answers
5k views

More Efficient Filtering Script

I'm exactly one day into writing Google scripts. I've managed to write one that will filter data in a spreadsheet with 24 columns and, for now, about 900 rows. The problem is, it's slow. Any ...
3
votes
2answers
5k views

How can I use Google's functions in a spreadsheet's script?

When I use a function such as =importXml("http://www.google.com", "//a/@href") in a Google Spreadsheet cell, it works just fine. However, when I try to use the same function in a script, it gives an ...
3
votes
1answer
173 views

Generate a formatted Google document from spreadsheet

As the title says, I have data (Q & A grouped by subject) that I have to export to a document file. As shown above, the source data on the left should be formatted like the example on the right ...
3
votes
1answer
2k views

How can you add a conditional statement to a =QUERY function

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...
3
votes
3answers
9k views

Google Scripts : How to distinguish Empty cells from Zero

I want to count a number of specific responses from a form where the response can be left empty, zero or any other number 'd'. The following code: var data = sheet.getRange("B2:L").getValues(); ...
3
votes
2answers
167 views

How do you subtract two time values in military format in Google Apps Scripts for a spreadsheet?

One column is labeled Checkin Time and the other is labeled Checkout Time and finally I have a column that is labeled Total Time Worked. The cells in the Total Time worked column should subtract the ...
3
votes
1answer
72 views

Google Document Date Script

I'm using a script that was posted here on Web Applications: Add Date to Google Document. I changed the way the date is formatted to this: ddMMMyyyy I wanted to have this, with the month being ...
3
votes
1answer
880 views

How Can I Solve Error “Exceeded maximum execution time” in Google Spreadsheet?

I found a script which I really need (link) but it gives an error (Exceeded maximum execution time) generally. How can I solve this error? This is the script: function getTotalSum(cell) { var ...
3
votes
1answer
188 views

Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

I am using the New Google Sheets, and I have created a "Master Document" that contains a multitude of sheets, with each expressing different kinds of data. I want a "Last modified" time stamp to ...
3
votes
1answer
1k views

Export all sheets in a spreadsheet to csv in Google Apps

Is there a way to export all sheets in a google spreadsheet to csv as individual files ?
3
votes
1answer
717 views

Can a Google Spreadsheet be recalculated if it's not manually opened?

We have some Google Spreadsheets that use the ImportHtml() to pull in some real messy tables. Then on on an alternate page we access the above data and pull it into a nice cleanly formatted table via ...
3
votes
1answer
3k views

Panel with Vertical Scroll Bar that allows navigating to different worksheets

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following ...
3
votes
3answers
16k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...