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)

3
votes
1answer
164 views

How can I sort all worksheets in my Google spreadsheet on open?

I have successfully been able to sort the first sheet in my Google Spreadsheet onOpen, but I'd like to sort all four spreadsheets onOpen and I can't make this work. I've tried quite a few variations ...
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
2answers
2k views

How do you add a custom font size in Google Spreadsheets?

How do I add a custom font size for a Google Spreadsheet I have? There are some defaults like 14pt, 18pt, 24pt and 36pt, but I would like to have a font size like 30pt, is it possible?
3
votes
1answer
25 views

Sum the totals for each product over all sheets in a spreadsheet

I'm trying to make a spreadsheet to take online orders from clients. I send them a price list, which they complete with their order and every time a client sends an order, it automatically adds a new ...
3
votes
1answer
252 views

Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen this already and want to use it, but I'm having issues trying to ...
3
votes
1answer
87 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
2answers
3k views

Create a popup in Google Spreadsheet

How can I create a popup in Google Sheets using script editor? I'd like to show a popup with a personalised notification. It should start like this: function onOpen() { var ss = ...
3
votes
1answer
657 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
10k views

send HTML email from a spreadsheet using a different alias

I've been frolicking around sending emails from Google Spreadsheet. The sent email has two requirements to fill: It needs to be send from my work-alias It needs to be as HTML I can do the former ...
3
votes
1answer
1k views

Automatically create a new row each day, on top of older rows

I am looking for a way to automatically create a new row on top of older rows each day. Inside of this row should be the current date.
3
votes
1answer
3k views

Google Script for pulling gmail addresses into a spreadsheet

This Google Script goes through and finds the emails in my gmail which are in a label called "LabelToGet" and send me a link to the spreadsheet created with these emails. It works well, but I have ...
3
votes
1answer
2k 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
1answer
4k views

How can I apply a script to only one specific sheet within a spreadsheet

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A function onEdit(event){ var sheet = ...
3
votes
2answers
7k 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
3answers
15k 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(); var ...
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
52 views

Timestamp in two columns

As a teacher, I am trying to create a Google sheet for the students to use as they sign in and out of class. I have successfully used code to auto insert a time stamp into column A if Column C is ...
3
votes
1answer
61 views

Can't find script insert option in Google Sheets

I want to add a script to Google Sheets. I watched a tutorial where to add a script one has to follow the path Insert>Script. But I found that there is no script option there. How can I get the ...
3
votes
1answer
163 views

Send selected cells from a row via email

I am starting with Google Scripts and I was hoping to create a sheet that could email selected cells from a row. What I want to do by selecting Send Email at the end of a row it then selects pre ...
3
votes
1answer
88 views

How to auto-update function based on background color

I've been referencing @w0lf's answer to help me calculate the sum of cells based on their background color in Google Sheets. From the comments and some other research it appears that changing the ...
3
votes
1answer
3k 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
1answer
1k views

Google Apps Script: copy document comments?

I'm working on the following script, which is supposed to look at the current document, grab its comments, make a copy, and copy the comments to the new document. However, I seem stuck. I'm not ...
3
votes
1answer
63 views

Google Script Active Cell Validation

A Google Script I am writing that seems to be failing. The first part of this script is to validate that only a specific sheet is being changed. In this case column 3 of my sheet called ...
3
votes
1answer
151 views

Hide/Show Multiple Groups of Columns in Google Sheets

I found a script (on this post: I need to hide/show a group of columns in Google Sheets in a simple way ) that adds a dropdown menu with two options: Show Range X of Columns & Hide Range X of ...
3
votes
1answer
188 views

Google Spreadsheet Timestamp on Edit?

I got the major part of answer on this link: Google Spreadsheet Timestamp? The code on this link is below: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ...
3
votes
2answers
326 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
125 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
730 views

Google spreadsheets importrange() error: Loading Data

Is there any reliable alternative to ImportRange() in google spreadsheets? After some time the formula brakes, and shows "Error: Loading data... ", and it wouldn't work until I change anything in the ...
3
votes
1answer
2k 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
289 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
5k 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
1answer
279 views

How can I take the HTML/data from a Google Document and restyle it with CSS?

I have a list in a Google Document that I would like to restlye with CSS. Unfortunately Google Docs no longer allows using CSS/HTML styling within the app itself. What would I need to do to get ...
3
votes
1answer
473 views

How to report which checkboxes were left unchecked on a Google Forms submission?

I have a Google form that stores its responses in a Google spreadsheet. It's set up so that whenever someone submits the form, I receive an email report of the responses. There are many places in the ...
3
votes
1answer
2k views

How do I Sum Thoughout Different Sheets

How do I create a reference to a defined interval in several sheets? Something like this: =SUM(Sheet1:Sheet2!A1)
3
votes
1answer
1k views

Google Spreadsheets: add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger

At the end of each row filled in, using a custom Google Form, I'm adding three more columns: Date of creation Unique ID QR code (it is a list of computers) Unique ID and QR code are unique. QR ...
3
votes
1answer
296 views

Google Spreadsheet text cell formatting question

I'm trying to create some sort of automated formatting for certain columns (except headers of course.) to take a string of numbers (and previous formatting; which should be stripped.) and format it as ...
3
votes
2answers
3k views

Parsing timestamp in Google spreadsheet with script

I have a spreadsheet with a form that timestamps every time the form is submitted. I'm writing a script to change the background color of the timestamped cell if it is more than 7 minutes after a ...
3
votes
1answer
76 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: ...
3
votes
0answers
59 views

Create button “Add” to add a new row in sheets [duplicate]

I'd like to know how to add a new row copying all formulas from previous row .. What I'm looking for is as follows SR. NO ITEM CODE DESCRIPTION UNIT COST 1 5463345 ...
3
votes
0answers
96 views

Color entire row based on the status in Column L [duplicate]

How can I make my spreadsheet look like in the picture below? Starting row 5, I want to color the entire row based on the status in Column L.
3
votes
1answer
422 views

How can I create a Google Spreadsheet whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros (though I'd really like to learn how to use it, so if you have any good tutorials or tips, let me know). ...
2
votes
2answers
2k views

Is it possible to pull my Company directory into Google sites intranet?

We use Google apps for enterprise, and I want to build an intranet, based on the Google sites intranet template In the Directory area, I want that the list will be updated from my contact/company ...
2
votes
3answers
3k views

how do I get named ranges in Google Spreadsheets to be more reliable/current?

I'd like to pull values from a named range into a calculation. I have this function: function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } Seems pretty simple. I ...
2
votes
5answers
9k views

Force users to update password every 3 months for Google apps?

I manage a domain and I want to force all users to change their password every 3 months. Is there a way to do this?
2
votes
2answers
50 views

Prevent “invoked too many times per second” error in Google Apps script

I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range ...
2
votes
2answers
55 views

Truncating the Title in the HYPERLINK function in Google Spreadsheet

I have a number of URLs that I first convert from text to the HYPERLINK function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached ...
2
votes
1answer
380 views

Multiple auto-populated select boxes Google Sheets Script UI

I found an example of how to auto-populate a drop-down select box from a Google Sheets range, for a Google Apps Script UI that enters new rows in the spreadsheet. I have so far been unable to figure ...
2
votes
3answers
144 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...