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)

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
366 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
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?
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 ...
1
vote
1answer
15 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 ...
0
votes
1answer
19 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 ...
3
votes
1answer
80 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: ...
1
vote
0answers
34 views

How to embed Google Sheet into Google Document and keep the Google Sheet format

How do I embed a Google Sheet into a google document and keep the Google Sheet format in the document? I have got this far with the script but it's not working function updateData() { var ssUrl = ...
1
vote
1answer
33 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 ...
2
votes
2answers
262 views

onEdit only updates top row after pasting (editing) multiple rows

I'm using a Google Spreadsheet and have a script that updates the "last email" column with a date stamp when an employee enters an email address into the prior column. The problem occurs when my ...
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 ...
4
votes
3answers
7k 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 ...
0
votes
1answer
14 views

Is there a way to print the source code of Google App Scripts?

I am an old fashioned programmer and like to print the source code of Google App Scripts. Is there a way to do that?
-1
votes
2answers
32 views

Fetching JSON in Google Sheets (ImportJSONAdvanced not defined?)

Currently working on fetching data from an API with Basic Authentication. Through this script: https://gist.github.com/anemitz/8cc6ef6c41ea4cefe51a13fbd6d8676a function ...
17
votes
7answers
18k 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: ...
2
votes
1answer
44 views

Is there a way to automatically duplicate 8191 google sheets and name then 1-8191 + copy their urls and add them 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) ...
2
votes
1answer
152 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
27 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 ...
2
votes
2answers
48 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? ...
2
votes
1answer
21 views

Can I set a trigger to run every second?

While searching for answer to How can I trigger a function when switching sheets within a spreadsheet? I noticed that the Triggers for current project dialog has an option for specifying a trigger ...
-1
votes
0answers
19 views

Concatenate values including column headers

I am using the following script that was kindly supplied in another answer : function mySummary(header, range) { var output1 = []; for(var i=0, iLen=range.length; i<iLen; i++) { var ...
0
votes
0answers
24 views

Set a send email account in Google spreadsheet

I have 3 email accounts, two gmail (a "corporate account" and a work account) and one personal account not via gmail. We collect requests in a google spreadsheet, where I want to add a hot mailto ...
1
vote
1answer
220 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 ...
2
votes
0answers
13 views

google docs, make summary sheet based on tags

I have a document in google docs. It is very long and I want to tag and extract only certain paragraphs into a second document that is a summary of the first document. I want to be able to dynamically ...
0
votes
1answer
30 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
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 ...
1
vote
1answer
20 views

Timestamp code for Google Sheets

I am using this script in order to put a time stamp in column B when someone edits in a row. However, I need it to place the time stamp if someone edits a row in column A. I don't need it to place a ...
2
votes
1answer
28 views

ImportRange as dependent function

I am trying to write a script which set the function importRange in a specific cell, using us KEY the content of another cell. I have the list of the KEYs in Sheet2 and here is what I wrote so far: ...
1
vote
1answer
18 views

What is a cut and paste approach to converting a chunk of a google spreadsheet to markdown table?

I maintain my web site in a version of markdown, with the common table extension. I maintain my inventory as a google spreadsheet. I'd like a quick way to copy paste a chunk of a spreadsheet, and ...
-1
votes
0answers
21 views

Export data from sheet to already created sheet

1) Find Google Sheet (Sheet 1), 2) Filter by a Column for specific name, 3) Export filtered rows to an already created Sheet (Sheet 2), 4) Automatically update Sheet 2 when Sheet 1 is edited. ...
38
votes
7answers
53k 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 ...
1
vote
0answers
36 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 ...
2
votes
1answer
30 views

Delete events from Google Calendar with a specific word in the title

I'm trying to delete specific events on my Google calendar, I've found this script that delete events with a specific title: function delete_events() { //take care: Date function starts at 0 for ...
1
vote
1answer
28 views

What is the difference between creating a menu with .getUI().createMenu() and .addMenu()?

What is the difference between these two ways of getting the same thing (a new menu item)? Version 1 function onOpen(e) { SpreadsheetApp.getUi() .createMenu('My Menu') .addItem('My ...
3
votes
2answers
27 views

How to restrict a Google Script to a certain cell range?

I'm working in an automatic script that shows the last modified dates of some cells within a sheet. So far, I've succeed with the sheet restriction adding an if condition, but I want to restrict this ...
7
votes
3answers
2k views

Google Spreadsheet generate random numbers with Math.random()

I'm trying to write a script function, which calls the Javascript Math.random() to generate a random number between [0, 1), like this: function f() { return Math.random(); } and I call it from a ...
1
vote
1answer
151 views

Trying to send email from Google Sheets with image attachment

I'm trying to send an email from within Google Sheets and then format it via some joins to send to an address for my Blogger account. I need to attach the image file apparently as the image isn't ...
2
votes
1answer
38 views

Auto Sort Google Sheet with onEdit function

The following function successfully sorts my file (containing 3 sheets) individually. When I hit the "play" button on the script, it properly sorts (as expected) the current (active) sheet. Since my ...
2
votes
1answer
33 views

How to select multiple, non-consecutive cells in Google Sheets using a list of the cell addresses

I have a very long, comma-separated list of cell address coordinates, shortened here for the sake of brevity: C3,C7,C17,C56. I would like to select these cells. Is this possible to do by pasting the ...
0
votes
0answers
72 views

Forwarding multiple emails not on string, how do I find Google scripts?

I found the suggestion on labelling the emails and then using Google scripts, but I can not find G scripts. Any suggestions, please?
2
votes
1answer
465 views

Trigger Script in Spreadsheet in Similar Fashion to a Link

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a ...
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 = ...
0
votes
0answers
52 views

Script to create an edit response URL in Google Sheet sometimes fails

I have been using the "FormMule" addon to create "Form Edit URL" apart from mail merge. However, the triggers for the addon seemed to have stopped working. I found this post answered by Rubén: Show ...
2
votes
2answers
548 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. ...
4
votes
1answer
269 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 ...
1
vote
1answer
68 views

Export “to” email addresses from Gmail

I found a similar question here: Get e-mail addresses from Gmail messages received but the difference is that I want "to" addresses, not "from". I have a catch-all email address at my domain. Except ...
2
votes
1answer
32 views

Add email hyperlink to spreadsheet

I have a spreadsheet where I track incoming email questions and outgoing responses. As questions come in, I have a script that appends a row (using .appendRow()) with information in the first few ...
1
vote
0answers
23 views

Auomatically lock/protect a range or row based on date

Any one can help how to do a scripts for auto lock / protect range/row by date I have try but this script only lock a cell only ( cell M:M ) a = SpreadsheetApp.getActiveSpreadsheet(); var s = ...
2
votes
1answer
55 views

Can I add two calculated fields to a Google Spreadsheet based on a Google Form?

I am new to Google Scripts and am hoping for some guidance on improving a manual process I complete in Google Sheets. To analyse the data I receive on a Google Form I run, I manually add-in (or copy ...