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
8k 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
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
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
1answer
3k 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
6k 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
35 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
35 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
103 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
62 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
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
13k 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
1answer
35 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
48 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
80 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
150 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
277 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
104 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
661 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
1k 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
251 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
4k 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
241 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
440 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
3answers
6k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
3
votes
1answer
289 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
0answers
95 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
376 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
2k 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
8k 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
46 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
1answer
264 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
140 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 ...
2
votes
1answer
5k views

Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn't open?

We have a Google Spreadsheet that pulls data from an external source via an IMPORTDATA() function and then a Google Apps Script that is on a timed trigger that performs other things with the imported ...
2
votes
1answer
338 views

Updating logging sheet from another sheet doesn't work after inserting code offset

I am trying to log the data, formulas and times of cell edits from one sheet into a logging sheet. The problem I am having is that it only seems to record changes made in the logging sheet. This has ...
2
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?
2
votes
2answers
26 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
49 views

Access Google Spreadsheet revision history through API

Is there any way I could access Google Spreadsheet revision history through the API? The reason for that is I have a lot of sheets and simply relying on build-in revision history does not suffice to ...
2
votes
1answer
34 views

How can specific columns of a Google spreadsheet be exported as CSV?

I have a Google spreadsheet with 10 columns of data. I would like to export columns A, C, E, F as csv. I do not need to export the entire spreadsheet. Can this be done? I do not mind using Google ...
2
votes
1answer
63 views

How do I use a negative DURATION formatted cell from Google Spreadsheets in Google Scripts?

I'm using Google Spreadsheets and I have a sheet in the following format: Start | End | Partial result | Expected | Total 08:00 | 12:00 | 04:00 | 05:00 | -01:00 14:00 | 18:00 | ...
2
votes
2answers
653 views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from http://webapps.stackexchange.com/a/50149/88163. ...
2
votes
2answers
242 views

Incrementing value read from spreadsheet exploding. 1 + 1 = 200 000 000 000

I read a value (in this case 7) from a Google Spreadsheet with a script, increase that value using source++ and write the increment back to the spreadsheet. I expect a series of 7, 8, 9, 10. ...
2
votes
1answer
3k views

Date and time stamp automatically added in column [duplicate]

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
2
votes
1answer
609 views

Arrayformula with dates before and after

Here is what my spreadsheet looks like I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula: ...
2
votes
2answers
60 views

Google Sheets/Forms/Script: How to send an email then replace “@” with “#”

I am trying to create a script where the user gets an email (column 1) with the body being column 2. However, after that, I want to replace the "@" in the email with a "#" so that they do not get the ...