Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
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 ...
Robert Koritnik's user avatar
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 ...
kirk tab's user avatar
  • 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 ...
RichGonzalez's user avatar
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?
Edward Tanguay's user avatar
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 ...
Jacob Jan Tuinstra's user avatar
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 ...
Arvind's user avatar
  • 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 ...
Robert Koritnik's user avatar
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 ...
user avatar
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
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
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 ...
nicks's user avatar
  • 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 ...
user275230's user avatar
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, ...
Arie's user avatar
  • 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. ...
bgoodr's user avatar
  • 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 ...
ale's user avatar
  • 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....
John Moffitt's user avatar
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 ...
JCr_BL10's user avatar
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,...
doubleunary's user avatar
  • 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 ...
Bryce's user avatar
  • 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 ...
Ger Murphy's user avatar
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, ...
Koen's user avatar
  • 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 ...
Kristof Pal's user avatar
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 ...
Trevor's user avatar
  • 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)
Jacob Jan Tuinstra's user avatar
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 ...
gills's user avatar
  • 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 ...
Riccardo's user avatar
  • 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: ...
Senseful's user avatar
  • 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 ...
user avatar
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 &...
MetaEd's user avatar
  • 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, ...
Paul-Michael Escolano's user avatar
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 ...
Ganswer's user avatar
  • 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 ...
WoJ's user avatar
  • 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 ...
villares's user avatar
  • 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 ...
William's user avatar
  • 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 ...
John Faig's user avatar
  • 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 ...
allindal's user avatar
  • 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 ...
Kelly Jensen's user avatar
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 ...
Johnathan's user avatar
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 ...
user68137's user avatar
  • 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 ...
Chris Weems's user avatar
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....
London Rabbit's user avatar
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 ...
John's user avatar
  • 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 ...
Jim's user avatar
  • 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....
Roy Koldaro's user avatar
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 ...
michael's user avatar
  • 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 ...
Marco's user avatar
  • 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 ...
que syrah sarah's user avatar
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, ...
orschiro's user avatar
  • 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 ...
user avatar
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 ...
hazymat's user avatar
  • 181

1
2 3 4 5
7