Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,790
questions
114
votes
6
answers
317k
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?
- 6,833
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
...
- 2,205
68
votes
3
answers
248k
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: ...
- 903
67
votes
13
answers
100k
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:
...
- 30.7k
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.
- 727
49
votes
13
answers
111k
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 ...
user6757
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 ...
- 451
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 ...
- 541
42
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 ...
- 22.8k
42
votes
6
answers
203k
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 ...
- 784
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?
- 3,207
41
votes
5
answers
90k
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 ...
- 6,043
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 ...
33
votes
6
answers
48k
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?
- 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.
- 481
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 ...
- 1,461
31
votes
6
answers
47k
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, ...
- 1,157
27
votes
1
answer
46k
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 ...
- 373
22
votes
3
answers
66k
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 ...
- 331
22
votes
5
answers
50k
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 ...
- 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?
- 669
21
votes
4
answers
72k
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 ...
user79865
21
votes
3
answers
51k
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 ...
- 2,205
20
votes
3
answers
194k
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 ...
- 309
20
votes
3
answers
50k
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 ...
- 4,282
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 ...
- 706
20
votes
2
answers
25k
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 &...
- 3,122
18
votes
3
answers
29k
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.
...
- 183
17
votes
4
answers
13k
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....
- 1,383
17
votes
1
answer
51k
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 ...
- 433
16
votes
5
answers
42k
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 ...
- 277
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 ...
- 1,976
15
votes
1
answer
62k
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 ...
- 489
15
votes
3
answers
22k
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).
- 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 ...
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. ...
- 1,878
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.
- 433
14
votes
2
answers
17k
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, ...
- 5,468
13
votes
3
answers
62k
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 ...
user9904
13
votes
3
answers
81k
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 ...
- 303
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 ...
- 131
13
votes
2
answers
70k
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 ...
- 233
13
votes
1
answer
57k
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 ...
- 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 =...
- 131
13
votes
2
answers
18k
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)
- 131
13
votes
2
answers
62k
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 ...
- 52.3k
13
votes
1
answer
44k
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 ...
- 6,833
13
votes
1
answer
17k
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 ...
- 231
12
votes
3
answers
94k
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 ...
- 30.7k
12
votes
2
answers
10k
views
How to search notes in Google Sheets?
My spreadsheet has many notes added to its cells. I would like to find the cells containing a certain word, phrase or number. How to do this?
user135384