Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
74 votes
9 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 ...
22 votes
3 answers
63k 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
105k 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 ...
41 votes
11 answers
157k 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?
20 votes
3 answers
48k 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 ...
16 votes
1 answer
44k 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 ...
  • 423
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 ...
21 votes
7 answers
77k 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?
21 votes
4 answers
70k 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
13 votes
1 answer
54k 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
34 votes
4 answers
138k 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 ...
30 votes
6 answers
46k 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,147
15 votes
3 answers
10k 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,820
13 votes
2 answers
61k 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 ...
  • 51.8k
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....
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 ...
1 vote
1 answer
1k 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 ...
20 votes
7 answers
79k 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 ...
  • 655
8 votes
1 answer
10k 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 ...
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
6k 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 ...
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)
4 votes
2 answers
10k 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
4 votes
5 answers
16k 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
0 votes
1 answer
3k 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,...
  • 10.6k
0 votes
1 answer
209 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 ...
  • 529
67 votes
12 answers
95k 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.2k
19 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,094
11 votes
3 answers
14k 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
11 votes
3 answers
45k 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, ...
10 votes
8 answers
11k 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
35k 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 ...
  • 347
7 votes
1 answer
31k 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
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
27k 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
29k 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 ...
5 votes
5 answers
27k 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 ...
5 votes
3 answers
77k 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 ...
3 votes
2 answers
10k 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....
44 votes
12 answers
104k 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
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
15 votes
5 answers
40k 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 ...
  • 267
14 votes
1 answer
56k 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 ...
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,266
13 votes
3 answers
57k 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 ...
  • 181

1
2 3 4 5
7