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
2answers
27 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 ...
0
votes
0answers
8 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
19 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
58 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) ...
2
votes
1answer
168 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 ...
0
votes
2answers
53 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
58 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? ...
1
vote
1answer
52 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 ...
8
votes
5answers
8k 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
1answer
221 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
vote
0answers
283 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
32 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
11 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 ...
28
votes
9answers
34k 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
400 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 ...
1
vote
1answer
20 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
30 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
94 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
46 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
34 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
272 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
16k 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
17 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
42 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: ...
3
votes
1answer
28 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
1answer
27 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 ...
0
votes
0answers
25 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 ...
2
votes
0answers
16 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 ...
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
25 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
19 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 ...
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 ...
2
votes
1answer
37 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
29 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
162 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
40 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
34 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
531 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
4k 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
64 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 ...