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)

37
votes
6answers
52k 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 ...
27
votes
8answers
31k 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 ...
16
votes
6answers
16k 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: ...
14
votes
2answers
5k 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 ...
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
3answers
30k 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 ...
9
votes
3answers
7k 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. ...
9
votes
2answers
4k 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 ...
8
votes
1answer
125 views

Google Sheets - Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
7
votes
6answers
8k views

Automated solution to delete certain Gmail messages older than n days

I need to delete certain Gmail messages older than n days (I can get the whole set though a search string). I know this is not possible directly within Gmail (see for instance this question) so I am ...
7
votes
3answers
6k 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 ...
7
votes
1answer
2k 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 ...
7
votes
3answers
31k 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 ...
7
votes
4answers
7k 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
1answer
2k views

Place cell data from a Google Spreadsheet into a Google Document

I'm trying to populate an invoice. I have a Google Spreadsheet that calculates hours per project, and I want to bring that into a Google Document, but despite much searching, have found no way to do ...
6
votes
1answer
1k 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 ...
6
votes
2answers
530 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
6k 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
1k 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
2answers
12k 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
4k 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
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
46 views

Probability Density Function of a Chi Distribution

I'm trying to write an extension for Google Sheets to add the CHIDIST() function as in Microsoft Excel and LibreOffice Calc. My current attempt does not produce the same results as the LibreOffice ...
5
votes
1answer
45 views

error making a copy of google app script

I'm trying to duplicate a Google app script file in Google drive that I own, but it gives me this error Limit Exceeded: Create. This is when I'm in the file and I use File --> Make a copy... When I ...
5
votes
3answers
21k 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 ...
5
votes
1answer
133 views

In Google Sheets how do I duplicate a sheet along with its permission

In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The ...
5
votes
1answer
7k 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 ...
5
votes
1answer
1k 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 ...
5
votes
1answer
48 views

Test Google Spreadsheets Add-On with custom functions

I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions ...
4
votes
4answers
2k views

Split, reorder, concat inline in Google Spreadsheets

I want to split a cell, reorder the pieces and recombine. How do I do that inline. For example: | name | reordered name | | Page,Larry | Larry Page | | Brin,Sergey | Sergey Brin | ...
4
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 ...
4
votes
1answer
3k 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
2answers
743 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?
4
votes
1answer
35 views

Update column with last update of row in Google Sheets… except the header

I've been using this code to find the most recent update of a given row: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var r = s.getActiveCell(); if( r.getColumn() != 5 ) { ...
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
2answers
402 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
1answer
428 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
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 ...
4
votes
1answer
195 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
371 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
75 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
1answer
2k 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 ?
4
votes
2answers
909 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 ...
4
votes
2answers
6k 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 ...
3
votes
2answers
218 views

Ways to learn google apps script language? [closed]

I've been looking for a good way to automate my google docs and sheets on drive, buy haven't found specific answers or even general ones I can modify to work for my specific requirements. I'm doing ...
3
votes
1answer
57 views

Adding a Month to a Date in a Google Spreadsheet with a Script

I'm trying to create a Google App Script that adds one month to a date already on a Google Spreadsheet. In the example below I was trying to take the date from cell B2 (1/1/16) and add one month and ...
3
votes
1answer
265 views

Send email with an attachment located in Google Drive

I want to send a PDF attachment located on Google Drive from a Sheet. Code I'm using is: function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of ...
3
votes
2answers
2k views

Making all cells in a column links from the values within the cell?

I have a column of cells in a Google Spreadsheet with values such as: 512 2123 2342 3532 What I would like to do is convert all these into a hyperlink and keep the value as the link text: ...
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
74 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 ...