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
139k 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 ...
user avatar
21 votes
3 answers
60k 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 ...
user avatar
  • 321
44 votes
3 answers
103k 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 ...
user avatar
41 votes
11 answers
156k 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?
user avatar
20 votes
3 answers
45k 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 ...
user avatar
15 votes
1 answer
38k 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 ...
user avatar
  • 413
40 votes
5 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 ...
user avatar
21 votes
7 answers
76k 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?
user avatar
21 votes
3 answers
68k 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
52k 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 ...
user avatar
  • 141
32 votes
4 answers
134k 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 ...
user avatar
29 votes
6 answers
45k 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, ...
user avatar
  • 1,137
14 votes
3 answers
9k 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. ...
user avatar
  • 1,650
7 votes
3 answers
15k 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....
user avatar
13 votes
2 answers
60k 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 ...
user avatar
  • 51.2k
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 ...
user avatar
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 ...
user avatar
  • 625
5 votes
2 answers
14k 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, ...
user avatar
  • 61
3 votes
2 answers
9k 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 ...
user avatar
  • 77
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 ...
user avatar
4 votes
5 answers
15k 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 ...
user avatar
  • 41
0 votes
1 answer
176 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 ...
user avatar
  • 479
0 votes
1 answer
2k 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,...
user avatar
  • 6,763
66 votes
12 answers
90k 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: ...
user avatar
  • 30k
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 &...
user avatar
  • 3,054
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 ...
user avatar
  • 411
7 votes
10 answers
34k 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 ...
user avatar
  • 327
10 votes
3 answers
38k 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, ...
user avatar
6 votes
2 answers
27k 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 ...
user avatar
5 votes
5 answers
26k 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 ...
user avatar
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 ...
user avatar
  • 223
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 ...
user avatar
5 votes
3 answers
74k 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 ...
user avatar
  • 119
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)
user avatar
3 votes
2 answers
6k 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 ...
user avatar
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....
user avatar
6 votes
1 answer
29k 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 ...
user avatar
  • 83
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 ...
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 ...
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....
user avatar
43 votes
11 answers
97k 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
49k 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 ...
user avatar
  • 345
13 votes
5 answers
37k 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 ...
user avatar
  • 247
12 votes
3 answers
51k 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
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 ...
user avatar
  • 249
14 votes
2 answers
16k 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, ...
user avatar
  • 5,148
6 votes
1 answer
44k views

Google Spreadsheet Timestamp? [duplicate]

I'm looking for some help with my Daily Work Log spreadsheet. I've search and read many different version of making a "TimeStamp" in Google spreadsheet. And it seems I don't have enough experience for ...
user avatar
  • 81
13 votes
1 answer
50k 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 ...
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 ...
user avatar
  • 181
5 votes
2 answers
26k 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 ...
user avatar

1
2 3 4 5
7