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)

0
votes
0answers
1 view

What steps should I take to build a secured form and have inputs post to a Google doc?

I am looking to automate some of my task. I have a Google account & a Dropbox account. I'd like to have the option for uploads into folder as well. I'm willing to collaborate on GitHub if anyone ...
0
votes
0answers
11 views

OnSubmit function, google forms and google apps script

If the number of rows increases, does retrieving a particular value using the below getValue function performs slower? Is there a better and more efficient way instead of retrieving a single Value ...
0
votes
2answers
46 views

How to sum cells in Google sheets that contain numbers and text

This has already been addressed in Excel but I was wondering how to do the same in Google Sheets. So suppose the B cell has this formula: ="frontend (days) total: "&sum(B2,B10,B14,B20) and the ...
18
votes
8answers
19k 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: ...
0
votes
1answer
13 views

Restrict the range of a script that replaces input data to a certain range

There is this question regarding adding checkbox functionality to Google Drive: Checkbox function to automatically add to total. And a great answer is: In the Google Sheets spreadsheet go to Tools ...
2
votes
1answer
95 views

Using Google Apps to handle customized after hours auto responses to support based email inbox

THE SETUP: Our company has a primary support email that all of our clients use to generate helpdesk tickets. It is a google apps inbox using helpdesk@%our primary domain% Our ticketing support auto ...
-3
votes
1answer
41 views

How to convert VBA to Google Sheets? [on hold]

I have VBA Code that every cell only can write one time. Does anyone know of a script that I can use in Google Spreadsheet that will do the same? Private Sub Worksheet_SelectionChange(ByVal Target As ...
-4
votes
1answer
32 views

Comparing a date in a spreadsheet with today's date using Apps Script [closed]

I have the following code but am facing issue with the "IF": it gives me undefined when the email is sent. In row [i][10] to row [i][16] iI have dates mentioned. I know I am doing something wrong in ...
2
votes
1answer
197 views

Dropdown list based on the result of another cell's dropdown

I want to populate a dropdown list based on a selection of another cell's dropdown list. Here is my spreadsheet. E.g., if user does not make a choice from D20 dropdown menu, therefore F20 dropdown is ...
3
votes
1answer
47 views

How to know when the contents of a PDF in Google Drive are indexed for searching

I wrote a nice routine that allows me to get file Ids of 200 PDF files (uploaded some minutes ago onto my Google Drive) and setValue of a Google Sheet with those Ids. In order to get the correct file ...
0
votes
0answers
21 views

How to stop Google Apps Script responding to noreply

I have a script that I have adapted to suit my needs. function autoReply() { var interval = 5; // if the script runs every 5 minutes; change otherwise var wkend = [6,0]; // 1=Mo, 2=Tu, 3=We,...
2
votes
1answer
25 views

Copy submitted form data to new automatically generated Google sheet

I have created a new sheet for every new name entered in the form using the following code. function formSheet(e) { var sheetName = e.namedValues['Name'][0]; SpreadsheetApp.getActiveSpreadsheet()....
7
votes
4answers
26k 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 ...
1
vote
1answer
18 views

Auto-create and name new sheet in Google sheets from names being filled from a google form

How can I automatically create and name new sheets in a google spreadsheet from a list of name which are being input in the sheet from a google form.I would like each new sheet to be created when a ...
2
votes
2answers
28 views

Run onEdit script in one sheet only

I have this Script so far, function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var r = s.getActiveCell(); if( r.getColumn() != 2 ) { //checks the column var row = r.getRow(); ...
1
vote
1answer
76 views

Google Sheets - Keep multiple sheets synced (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
-1
votes
1answer
47 views

Getting e-mail adresses using a filter in Google Sheets /w scripts

I am trying to extra email replies from gmail. I already found some code that is supposed to work. My question is: how can one expand it to support a filter as a variable? (in this case so a filter ...
1
vote
1answer
230 views

Download list of contact information and check if Google Account

I'm looking for a way I could: retrieve a list of all my contacts and their email's and In order to retrieve a list of all of my contacts into a Google spreadsheet, I exported them as a Google CSV ...
-1
votes
0answers
26 views

Using Google URL Shortener API in Google Sheets for shortening bulk URL's

I've done everything I can think of but still cannot get this script to work. I keep receiving a "Action Not Allowed" error when run. function onOpen() { SpreadsheetApp.getUi() .createMenu("...
1
vote
2answers
704 views

Make a counter, that counts cell change

I have a cell, let us call it A1, that contains an IF function. Cell A1 changes between two types of text: Healthy Injured What I want is a counter in for example B1 that counts how many times A1 ...
1
vote
1answer
36 views

Any way to send Gmail Out of Office/Auto-reply at the same times every week?

Because my clients bother me out of hours I would like to have my Out of Office respond out of hours without having to set it manually every day. To comlicate matters further I want to send one ...
1
vote
0answers
23 views

Is there a script to draw a list of variables from a table to Google Forms?

I would like to have a field in Google Forms that allows the user to select a value from a "List" of values. The options in the list being drawn from a list in the sheet itself. This allows me to add ...
2
votes
1answer
47 views

Sending emails onSubmit using Google Apps Script Web Forms

Is it possible to send emails onSubmit() with Google Apps Script Custom Web Forms just like we can do using mailApp with Google Forms? I am currently transferring google forms code into html forms ...
0
votes
3answers
74 views

How do I compare the values of two columns and return the result in new columns?

EX. https://docs.google.com/spreadsheets/d/1CwFXSwIgnPdMocbrhTFZbEPRyXZUiovvWNy9MtOagxE/edit#gid=0 I wanted to compare two column "fix version" and "Affected version" and return the result bases on ...
0
votes
3answers
5k views

Parse HTML through Google Spreadsheet using scripting

I'm trying to speed up the process of getting values from an HTML page to a Google Docs Spreadsheet through scripting. I remember Microsoft Excel had the import data function, but I don't own a copy ...
3
votes
1answer
39 views

Can I host custom forms using Google Apps Script?

Is it possible to have custom forms created using Google Apps Script to have their own URL just like Google Forms do and be publicly available? Can I host these custom forms somewhere without using ...
1
vote
1answer
25 views

Script to delete a dynamic range of rows

I'm looking for a script that allows me to delete a dynamic range of rows. Basically I have a script that adds rows to the bottom of a list but I want another script that will delete all rows in the ...
3
votes
1answer
124 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: http://www.jacorre.com/tutorial/remove-duplicate-rows-google-...
2
votes
2answers
33 views

Organize employee time log into a table with a row for each employee

I want to get all the Timestamp of an employee (EmpID) from one sheet to another using Google apps script or otherwise. Explanation In Google Sheets, Sheet1 has the following items in 3 columns | ...
1
vote
2answers
34 views

JavaScript To Dynamically & Daily Select The Next Worksheet In An Open Google Sheets File

At work using Chrome, I have a Google Sheets file saved on my Google Drive folder that's shared with me with edit privileges that I keep open on my MacBook Pro. After I've originally opened the file ...
1
vote
1answer
12 views

How to use user-defined functions for custom formatting?

I’m writing a simple Google spreadsheet to list every account number which could come in handy to our non-profit in the future. Of course, one can expect some typo will get in some point in the future....
1
vote
1answer
41 views

How can I automatically set “sheets last updated” in a cell on Google Docs Spreadsheets?

Simple task!.. output a date and time every time something in the sheet is edited! function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets("Data"); var ...
1
vote
0answers
28 views

Google Forms - Show unique ID on the form (before submitting)

I need an anonymous form (no names, emails etc) that shows unique ID so that if a person decided to reveal that ID to me I will be able to find his/her answers in the spreadsheet. The ID can show on ...
0
votes
1answer
79 views

Unable to import pricing info from toysrus.com into a Google Spreadsheet

I'm trying to pull live pricing from a given toysrus.com item into a Google Spreadsheet (one item/price per row), and am having no luck whatsoever. Importxml(url, xpath) does not work (similar to the ...
0
votes
0answers
10 views

Google Spreadsheets: add hours to date problem

I've encountered some kind of strange problem with Google spreadsheets. I've 2 cells, in one I've date, lets say its 10/10/2010, in 2nd one is hour, 12:00:00. I get those from input cells, but when I ...
0
votes
1answer
27 views

Sync multiple cells so that if any one of them is changed, the others change as well

I have 4 hard coded cells on 4 different sheets (within a google sheet file) that I'd like to keep synced. If any of those four cells have been changed, I'd like the other three cells to be updated to ...
2
votes
1answer
60 views

Is there a way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet?

Background: For a genealogy project, I've created a Google sheet where participants enter all details of one ancestor. (FILEXXXX) In another Google sheet (not just another tab/sheet, another FILE) ...
0
votes
2answers
60 views

“Select Users from organization” field with Google Forms?

So here is my scenerio, I have a Google form that allows users to send a co-worker a digital greeting card, but they have to manually enter the email address of the person they are sending it to. The ...
3
votes
2answers
68 views

How can I trigger a function when switching sheets within a spreadsheet?

How can I trigger a function when switching sheets? Let's say I'm viewing a multi-sheet spreadsheet. Now I want a function to run whenever I switch from one sheet (tab) to another. Is that possible? ...
8
votes
5answers
9k 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 ...
1
vote
0answers
344 views

Google sheets query returns “loading…”

I've put together a web application that relies on a series of Google components. Google Forms collects data from users Google sheets saves the data (automatically) Additional "calculated" columns ...
0
votes
1answer
38 views

Google apps script to check availability

I'm using google form+calendar to create a reservation system for my makers lab. The user fills out the form and an event is created on the calendar so the TA knows when to goto the lab to help the ...
0
votes
0answers
16 views

Loop through columns in different sheets, comparing the values

I have three sheets. One is for the item senders, one is for the receiving of the items, and the other times compares the sent and received. For the comparison to work I need to loop through columns a ...
29
votes
9answers
35k 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 ...
9
votes
2answers
5k 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 &...
9
votes
2answers
470 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 ...
2
votes
5answers
10k 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?
5
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 ...
2
votes
3answers
4k 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 ...
0
votes
1answer
56 views

Using Google URL Shortener API in Google Sheets for bulk URL shortening

Recently come across similar issue to this question, where I want to add use the Google URL shortener API in Google Sheets for bulk URL shortening. However, I keep running into this error: Rate ...