Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

68
votes
9answers
130k views

How can I set conditional formatting on a particular cell that depends on another cell's value?

In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1): A | B 1 | x 2 | 3 | 1 | x ...
46
votes
2answers
183k views

Link to specific sheet in Google spreadsheet

I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: ...
43
votes
7answers
60k 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 ...
40
votes
12answers
45k 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: ...
32
votes
2answers
22k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
32
votes
9answers
45k 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 ...
31
votes
4answers
16k views

How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?

Is it possible to convert text from a certain cell to RFC 3986 (http://url-encoder.org/) in a Google Spreadsheet? Lets say C4 has the value John Doe, then D4 must be John%20Doe.
30
votes
9answers
78k views

Is there any way to create a timestamp in a Google Spreadsheet?

I use PSPad as a text editor, which allows you to press Alt + D to insert a timestamp, e.g.: 2010-07-17 23:45:44 Is there a way to do this in a Google Spreadsheet?
30
votes
3answers
74k views

Auto-updating column in Google Spreadsheet showing last modify date

Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing ...
25
votes
9answers
37k views

How to specify the entire sheet as range in Google Sheets?

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 ...
21
votes
5answers
45k views

Is it possible to setup a filter in Gmail to delete message older than one month? [duplicate]

Possible Duplicate: Webapp to delete certain Gmail messages older than n days? The reason I need that is because I need to subscribe to some open source project mail list and I don't need to ...
21
votes
4answers
73k 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 ...
19
votes
5answers
15k views

Default sheet in Google Spreadsheet

Is it possible to mark a sheet as "Default" so that if anyone opens a Google Docs spreadsheet, the corresponding sheet is opened instead of the first one?
18
votes
6answers
73k views

How can I automatically set “last updated” cell in row Google Docs Spreadsheets?

How can I automatically set "last updated" cell in row Google Docs Spreadsheets ? I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this ...
18
votes
4answers
69k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
16
votes
3answers
37k views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
15
votes
4answers
4k views

Is there any way to script a Google Docs spreadsheet?

I have a really complex spreadsheet in Google Docs, and while it works, having the ability to script it a bit (à la VBA in Microsoft Office) would make it a lot cleaner. I heard that this is doable in ...
14
votes
2answers
19k 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 &...
14
votes
3answers
23k views

How to save temporary variables in Google Sheets formula?

I'm trying to create a formula in a Google spreadsheet which looks like this: if (x < 0, x + 1, x) It means if x is smaller than 0, return x + 1, else return x. However, x itself is an ...
12
votes
3answers
25k views

Cell reference with colour formatting

Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference cell's value. But if ...
12
votes
4answers
6k views

How to export / import a Google Apps Script as a file into a new spreadsheet?

I fear this may have an obvious answer, but I've been knocking my head over this and can't find it! When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro....
12
votes
1answer
5k views

How to create user-defined key bindings or shortcuts to run App Scripts in Google Docs

This question specifically applies to Google Documents, not Google Spreadsheets (but if the answer applies to both, all the better). Google Documents has built-in Keyboard shortcuts for Google Docs. ...
11
votes
4answers
22k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
10
votes
2answers
46k views

Calculating shaded cells

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
10
votes
1answer
21k 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 ...
10
votes
7answers
19k views

Is there a way to link a “Choose from a list” style question in a Google Form to a list of data (e.g. in a spreadsheet)?

Is there a way to link a "Choose from a list" style question in a form to a list of data (e.g. in a spreadsheet)? This would mean that as new options were added to the list in the spreadsheet (or ...
10
votes
2answers
4k views

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 ...
9
votes
1answer
16k views

Add a script trigger to Google Sheet that will work in Android mobile app

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the ...
9
votes
2answers
27k views

Convert all text to UPPERCASE in a Google Spreadsheet

I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells. How can I cause those cells to convert ...
9
votes
3answers
10k views

Pass range in Google Apps script custom function without using A1 notation

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to ...
9
votes
2answers
9k 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 ...
9
votes
1answer
3k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
8
votes
2answers
32k views

Basic date manipulation in Google script

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
8
votes
8answers
10k views

Automated solution to delete certain Gmail messages older than n days

I need to delete certain Gmail messages older than n days (I can get the whole set though a search string). I know this is not possible directly within Gmail (see for instance this question) so I am ...
8
votes
4answers
20k views

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?
8
votes
2answers
29k views

How to Refresh a cell in Google Spreadsheet?

I have a column in Google Spreadsheet that calls an outside webservice using URLFetch in a Google Script. The cell uses two parameters in that cell's row. After a change occurs where those two ...
8
votes
1answer
8k views

In Google Sheets how do I duplicate a sheet along with its permission

In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The ...
8
votes
3answers
7k views

Can I use other font weights in Google Documents?

How can I use other font weights (such as Book 300 for Open Sans) in my documents? Adding Open Sans only allows me to use Normal 400, for example.
7
votes
1answer
23k 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 =...
7
votes
3answers
8k 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 ...
7
votes
3answers
10k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
7
votes
2answers
1k views

Custom data transpose?

I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than ...
7
votes
1answer
22k views

Apply custom CSS styles to Google Forms

Google forms are presented for the user to embed in his/her website as an iframe, which means they can't be styled by the website's own CSS. How can a Google Form be inserted into a website with a ...
7
votes
3answers
13k views

Duplicate Google Sheets' protected cells [duplicate]

I have created my spreadsheet with protected cells scattered throughout the document in Google Sheets. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep ...
7
votes
1answer
7k views

Export all sheets in a spreadsheet to csv in Google Apps

Is there a way to export all sheets in a google spreadsheet to csv as individual files ?
6
votes
2answers
40k 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 = SpreadsheetApp....
6
votes
3answers
45k views

How to format the date in this Google Docs script

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
6
votes
5answers
25k 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?
6
votes
1answer
32k 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 ...
6
votes
1answer
3k views

Automatically create a new row each day, on top of older rows

I am looking for a way to automatically create a new row on top of older rows each day. Inside of this row should be the current date.