Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
342
questions
74
votes
8
answers
140k
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
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
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
41
votes
11
answers
158k
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,197
42
votes
6
answers
36k
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
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
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
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
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
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 ...
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
1
vote
1
answer
3k
views
Hide/Unhide rows based on values in particular cells
I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i ...
- 11
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
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
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
7
votes
3
answers
16k
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....
- 173
2
votes
1
answer
2k
views
Add timestamp to two different columns in Google Spreadsheets
Need help in adding another function using the below script.
I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on ...
- 23
1
vote
1
answer
4k
views
Embed an interactive calculator in my website using Google Sheets
I have implemented a calculator in a Google Sheet, and would like to embed it in my website. There will be multiple users. They should be able to enter numbers and see the result, perhaps with a chart,...
- 13.2k
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
8
votes
1
answer
11k
views
Send email with an attachment located in Google Drive
I want to send a PDF attachment located on Google Drive from a Sheet.
Code I'm using is:
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of ...
- 81
5
votes
2
answers
15k
views
Add a 'creation date' value when a new line is edited
I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's an example. People can add tasks to column B and I will assign them to the right people in the team, ...
- 61
5
votes
1
answer
7k
views
Insert current date to cell based on content in other cell
Background:
I am trying to create a sheet which tracks my costs on daily basis. Instead of entering the current date everyday, I want the sheet to store the current day based on the content I enter ...
- 153
4
votes
5
answers
17k
views
How to make that a custom function update its result immediately
I'm using a custom function to read the sheet name (shown in the tab) and then return the name to the cell in the summary page. The script works, however it doesn't update when I change the name of ...
- 41
4
votes
1
answer
4k
views
How do I Sum Thoughout Different Sheets
How do I create a reference to a defined interval in several sheets?
Something like this: =SUM(Sheet1:Sheet2!A1)
- 22.8k
4
votes
2
answers
12k
views
Conditionally format row borders
I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet.
I'd also like to have the same top border across each row whose whole row is ...
- 87
0
votes
1
answer
231
views
Google Apps Script Javascript compatibility error
I have successfully created some code to extract data from a 2D array using A1 notation. Handy to avoid multiple calls to GAS services whit ordinary getRange calls.
While the code perfectly works in ...
- 579
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
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
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
11
votes
4
answers
50k
views
Is there a way to emulate Vlookup in Google Script?
I did some looking around and I'm struggling quite a bit in finding the answer.
I'm working on a Google Script for Google Sheets that works like a Vlookup, where you define a cell for it to look at, ...
11
votes
3
answers
15k
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 ...
- 249
10
votes
8
answers
12k
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 ...
- 423
8
votes
10
answers
37k
views
Importing data into a Google Forms response sheet
I have some Google Forms and would like to import legacy survey data into the responses spreadsheet, so as to benefit from the data presentation features (having the combined data set) but I've been ...
- 357
7
votes
1
answer
32k
views
Automatically "Allow Access" for IMPORTRANGEs in Google Sheets?
I have a set of Google Spreadsheets I want to reproduce (not share) for several users. These users are not going to be able to figure out how to Allow Access (for example, some of the IMPORTRANGEs are ...
- 93
6
votes
2
answers
28k
views
Get a list of Drive files into a spreadsheet
I am playing around with AwesomeTable and like the Post-It template. My use case is to curate a nice interface to a list of frequently used files. The list won't change dramatically over time, but ...
- 109
6
votes
2
answers
2k
views
Ways to learn Google Apps Script language? [closed]
I've been looking for a good way to automate my Google Docs and Sheets on Drive, buy haven't found specific answers or even general ones I can modify to work for my specific requirements. I'm doing ...
- 223
6
votes
2
answers
30k
views
How do I implement a script that creates a unique ID for a Google form submission?
I need to create a unique ID upon submission of a form which will be used as a project number for tracking. The number needs to be unique but not based on rows or other changeable data because ...
- 69
5
votes
5
answers
29k
views
Script to have Google Sheets jump to the next available row in column A automatically?
I'm using Google Sheets to keep track of inventory. As the year goes on, the sheet gets quite long, so it is a pain to scroll all of the way to the bottom each time we start a new day, end the day or ...
- 51
5
votes
3
answers
80k
views
Send an email with attachment using Google Apps Script [duplicate]
I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach a PDF to this email. The PDF file is located ...
- 119
3
votes
2
answers
7k
views
Script to add protection and remove the user running it?
We have a worksheet that will have multiple sheets, one in which they will edit and the others we want protected. This is an invoice/quote builder, so every time they have a new one they will be ...
- 31
3
votes
2
answers
11k
views
How to make Google Sheet jump to today's column when opened?
I'm trying to adjust this code so that I can jump to today's date which are all across row 2 in my spreadsheet.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss....
3
votes
1
answer
9k
views
How to Send Html Emails in Google Spreadsheet
Here's an example of a Google Docs Spreadsheet script that sends emails using Google spreadsheets. I want the email Sent in HTML not plain text. Can someone help me out?
function sendEmails() {
var ...
- 31
2
votes
1
answer
2k
views
Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns
Trying to determine a way that I can use a custom menu (.createMenu) with views that will allow me to show / hide specific columns. I need the capability to pick specific multiple columns and not ...
- 21
1
vote
1
answer
5k
views
Running multiple onEdit scripts
I am trying to run all three of these in any order but only the third runs:
1.
function onEdit(){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var editedCell = sh....
- 11
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
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
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
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
7
votes
1
answer
29k
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 ...
- 181