Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
114 votes
6 answers
320k views

Is there a way to insert today’s date into a Google Docs?

I've looked under insert > ...? but couldn't find anything. Can this be done via an inbuilt function or a custom script?
sam's user avatar
  • 6,899
74 votes
8 answers
141k 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 ...
Robert Koritnik's user avatar
69 votes
13 answers
102k 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: ...
Senseful's user avatar
  • 31k
68 votes
3 answers
250k 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: ...
JBWhitmore's user avatar
57 votes
7 answers
74k views

Can I get header numbering in Google Docs?

Is there a way to automatically number headings in Google Docs? It used to be possible with CSS but this feature isn't supported anymore in the new Google Docs version.
Martin Delille's user avatar
49 votes
13 answers
114k 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 ...
user avatar
44 votes
6 answers
209k 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 ...
cwd's user avatar
  • 804
44 votes
3 answers
106k 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 ...
RichGonzalez's user avatar
43 votes
6 answers
37k 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 ...
Jacob Jan Tuinstra's user avatar
43 votes
7 answers
70k 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 ...
Phira's user avatar
  • 541
42 votes
5 answers
92k 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 ...
Pacerier's user avatar
  • 6,143
41 votes
11 answers
159k 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?
Edward Tanguay's user avatar
34 votes
4 answers
142k 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 ...
Pythonista's Apprentice's user avatar
33 votes
6 answers
49k 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?
Rpj's user avatar
  • 733
32 votes
4 answers
19k 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.
Robin Alexander's user avatar
32 votes
6 answers
48k views

How to delete all events on many dates all at once but not the whole Calendar in Google Calendar?

How to delete all events on many dates all at once but not the whole Calendar in Google Calendar? I tried to click on a date that got an event to delete and dragged my mouse to 2 days after that date, ...
Arie's user avatar
  • 1,167
32 votes
9 answers
49k 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 ...
Evan Plaice's user avatar
  • 1,459
27 votes
1 answer
47k 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 ...
Probaton's user avatar
  • 373
23 votes
3 answers
67k 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 ...
kirk tab's user avatar
  • 341
22 votes
5 answers
51k 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 ...
michael's user avatar
  • 345
21 votes
7 answers
78k views

Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?

Is it possible in Google Forms to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?
Toby Allen's user avatar
21 votes
4 answers
73k 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 ...
user avatar
21 votes
3 answers
52k 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 ...
Robert Koritnik's user avatar
20 votes
3 answers
201k views

How to format the date in this Google Apps 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 ...
user72299's user avatar
  • 309
20 votes
3 answers
31k views

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code. ...
Elizabeth's user avatar
  • 203
20 votes
3 answers
51k views

How to import JSON data into a Google spreadsheet?

Google Sheets has an IMPORTXML function to import data from various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there a similar way to import JSON formatted ...
kenorb's user avatar
  • 4,392
20 votes
7 answers
80k 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 ...
Bryce's user avatar
  • 726
20 votes
2 answers
26k 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 &...
MetaEd's user avatar
  • 3,122
17 votes
5 answers
43k views

How to make Google Sheets jump to today's row when opened?

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened. In other words: The date column has the ...
Marco's user avatar
  • 287
17 votes
4 answers
14k 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....
cregox's user avatar
  • 1,383
17 votes
1 answer
53k 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 ...
Arvind's user avatar
  • 443
16 votes
4 answers
6k 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 ...
Benjamin Pollack's user avatar
15 votes
1 answer
65k views

Diference between onEdit or onChange trigger? [closed]

From what I've gathered online, onEdit only works when you edit a cell (not when a row is added or when a note/comment is added) and onChange will capture that a change has occurred and trigger when ...
que syrah sarah's user avatar
15 votes
3 answers
23k views

Can I set the number format to show exactly 2 decimals only when needed in Google Spreadsheet?

I want to set the number format as below: If the number have more than 2 decimals, round it to 2 only (e.g. 12.3456 -> 12.35). If the number is whole number, just let it be (e.g. 12 -> 12).
mobo's user avatar
  • 161
15 votes
3 answers
7k views

Trouble bulk deleting mail from gmail

I'm on a list-serv that gets emails from crons for several hundred servers. The volume of emails is about 1G/week, so to keep from bumping up against my 15G disk quota, I have to delete messages ...
Barton Chittenden's user avatar
15 votes
3 answers
11k 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. ...
bgoodr's user avatar
  • 1,938
14 votes
3 answers
18k views

How to apply autoformatting in Google Apps Script?

Neither shortcuts e.g. CTRL + Shift + f nor options provided by the taskbar: seem to be able to apply autoformatting in Google Apps Script.
030's user avatar
  • 433
14 votes
2 answers
63k 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 ...
ale's user avatar
  • 52.6k
14 votes
2 answers
18k views

Current date as a variable in Google Docs?

Is it possible to add (insert) a variable for the current date into a Google Docs that would automatically update each time I open the document? Some criteria: The date position should be variable, ...
orschiro's user avatar
  • 5,488
13 votes
3 answers
65k views

In Google Spreadshets, how can you loop through all available sheets not knowing the total count?

I'm keeping stats for my daughter's baseball team. I have a sheet for each game, each one identical in structure. I want to create a summary sheet that totals numbers from the other sheets. How ...
user avatar
13 votes
3 answers
82k 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 ...
anderstood's user avatar
13 votes
2 answers
62k 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 ...
salgire's user avatar
  • 131
13 votes
2 answers
71k 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 ...
capn's user avatar
  • 233
13 votes
1 answer
59k 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 ...
nicks's user avatar
  • 141
13 votes
2 answers
55k 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 =...
oohrogerplamer's user avatar
13 votes
2 answers
19k views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
Saul's user avatar
  • 131
13 votes
1 answer
45k views

Can I protect the formatting of a Google Sheets?

I’ve got a Google Sheets that has lots of formatting and formulas, in various columns, some columns are formula driven, and some columns that are user input driven. The spreadsheet is shared with ...
sam's user avatar
  • 6,899
13 votes
1 answer
16k 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 ?
Michael's user avatar
  • 489
13 votes
1 answer
18k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
Number8's user avatar
  • 231
12 votes
3 answers
99k views

Is it possible to use a loop in Google Spreadsheets?

I have the following document: +---+-----------+ | | A | +---+-----------+ | 1 | Foo (100) | | 2 | Bar (30) | | 3 | Baz (50) | +---+-----------+ I'd like to use a REGEXEXTRACT to extract ...
Senseful's user avatar
  • 31k

1
2 3 4 5
37