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
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
4k 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
265 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
0answers
448 views

How can I get the body of a Gmail message before the quoted text with GmailApp? [closed]

I'm programming a little Google App Script for which I need to get the real body of an e-mail (using gmail services). By real I mean the pure body of a particular message, without the text that ...
3
votes
1answer
267 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
2answers
38 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
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
1answer
34 views

Send email with PDF attachment located in Google Drive

I'm new to google apps script - coming from a VBA world. I want to send a PDF attachment located on Google Drive from a Sheet. Code I'm using is: function sendEmails() { var sheet = ...
2
votes
2answers
77 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
3answers
122 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
2answers
195 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
234 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
1answer
2k views

Date and time stamp automatically added in column

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
809 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 ...
2
votes
1answer
462 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
1k 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: ...
2
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 ...
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
1answer
13 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
19 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
2
votes
1answer
32 views

Clear range of cells

I'm trying to make a script for Google Spreadsheets (which is my first time), by clearing a range of some cells. So: for example I want to clear D5:D12 but not D13 and D14, but then again I want to ...
2
votes
1answer
23 views

Import Google Contacts to Google Spreadsheet

Google notice me : You do not have permission to call getContacts (line 3) Here is my code: function importFullName(input) { var CONTACT = ...
2
votes
1answer
158 views

Adding a script to Google Forms to identify incorrect phone numbers

I currently use Google Forms as a way for applicants to submit their forms. One of the fields that is required is for their contact information. In my country phone numbers have 11 digits. Is there a ...
2
votes
1answer
945 views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
2
votes
1answer
84 views

Formula value change after a script pastes into another range

I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with ...
2
votes
2answers
120 views

How can I apply a script to every sheet but the first one on a spreadsheet?

I've been working on a shared Google spreadsheet on Google Drive. The document has different sheets with a "Last Modified Date" Cell. I found an script for this to be auto-filled searching here: ...
2
votes
2answers
3k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
2
votes
1answer
732 views

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet?

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet? Some of the responses on one will be public and viewable by anyone, but others will be private.
2
votes
3answers
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 | ...
2
votes
1answer
299 views

Repeat range under each other in Google Spreadsheets

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this? So for example I have this range: Year | ...
2
votes
1answer
2k 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 = ...
2
votes
3answers
8k 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(); ...
2
votes
1answer
1k views

Batch copying/renaming in Google Docs

I need to copy and rename a base form (a spreadsheet) to several other forms in Google Docs. Imagine I have the following form: My Base Form - @name (@course) After running the script or whatever, ...
2
votes
1answer
35 views

Auto Sort data in Google sheet, but by sheet not range

I have found this script from this question to auto sort a column: How can I apply a script to only one specific sheet within a spreadsheet function onEdit(){ var sh0 = ...
2
votes
1answer
50 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" ...
2
votes
1answer
46 views

Importing columns across two Google Sheets in same spreadsheet

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members. First issue: ...
2
votes
1answer
47 views

Script to create a new worksheet in Google Spreadsheet

I want to be able to have a button on a Google Spreadsheet. The user clicks on the button and a new worksheet is created based on a template (or blank existing worksheet). The new worksheet should ...
2
votes
1answer
35 views

How to sort Google Sheets columns by row height

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet ...
2
votes
1answer
55 views

Using Google Apps Script to set cursor to Headers in Google Docs

I am trying to create a Google Apps Script that could help navigate through the headers. I have currently a code searching the position with the function "findText" that I used in a custom menu in my ...
2
votes
1answer
128 views

Unique ID Column

I have a Google Spreadsheet that I want to have a unique number created in column A when I enter information in column B. Below is the formula I currently have that works, but I am wondering if there ...
2
votes
1answer
80 views

How do I loop a script through all pages but the first one?

I'm very new to all this. I'm trying to write a script that sorts sheets by descending date order each time a new row is entered. I need the script to run on all pages but the first (the summary ...
2
votes
1answer
112 views

How do I get a weekday 12 weeks ago using in a Google script?

I'm having the following formula: =TODAY()-WEEKDAY(TODAY(),3)-84 and this gives me Monday, 12 weeks ago. I'm working on this code, but it gives me only a certain days back var twelweWeekAgo = new ...
2
votes
1answer
247 views

How do I refresh script results when a cell color is changed?

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. ...
2
votes
1answer
161 views

Run script when clicked on picture in published Google spreadsheet?

I used the script underneath in Google Spreadsheet and added an image that when clicked upon runs the GOTOSHEETONE function to navigate to sheet: SHEETONE. So far so good. However, when I publish my ...
2
votes
1answer
54 views

Google Sheets Passing Range from separate sheet into custom function

I have the following custom function I've built to basically reproduce a needle haystack type of functionality function ListBy (Range, Range2, needle) { var holder = []; if(typeof Range != ...
2
votes
1answer
767 views

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

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...