Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
14
votes
3answers
91k 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 ...
9
votes
2answers
38k 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 =...
32
votes
4answers
18k 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.
19
votes
2answers
43k views

How to import JSON data into Google Spreadsheets?

There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there any similar solution to import data in JSON ...
10
votes
1answer
4k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
6
votes
7answers
25k 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 ...
7
votes
2answers
44k views

Can Google Forms send emails automatically?

I used Google Forms to create a form for people to register for an event, by entering their name and email address. I would like to set it up so that when a person enters his name and email, the ...
3
votes
3answers
12k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
8
votes
2answers
34k views

How to show “Last edited” date and user on Google Sheets?

I've got a Google sheet that is updated quite regularly by a few people. I'd like to be able to view the doc, and at a glance—have a cell that shows a date (last modified) and another cell (user that ...
7
votes
1answer
29k 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 ...
6
votes
3answers
10k views

Setting multiple protected ranges with one range value inputted

Id like to set protected ranges over several sheets and ranges of the same spreadsheet in a Google spreadsheet. The normal way to do a range is 'Sheet One'!A:L What id like to do is be able to ...
5
votes
2answers
6k views

How can I automate Find & Replace to change font colours in Google Docs?

How can I automate a find & replace function to change the font colour of all suit symbols document wide? I write bridge articles (card game), and I need to automate this formatting constantly: ...
3
votes
2answers
12k views

Dynamic generation of sheet name from cell content

Is it possible to dynamically change the name of a sheet in Google Sheets based on the contents of a specific cell? For instance, I'd like my sheet to get its name from whatever is in cell A1... is ...
2
votes
1answer
5k 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 ...
2
votes
2answers
8k views

Insert column across multiple sheets

I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats). Is there a way to insert a column on one ...
2
votes
1answer
2k views

How to set input type = “tel” to the answer input field?

In my Google Form I've got multiple text questions with answers with data validatation set to numbers only. Unfortunately when I enter the answers using the mobile browser the keyboard shown is full, ...
2
votes
3answers
4k views

Having Google Sheets not recalculate a formula on page load

I have a Google Sheet with a list of addresses, and a column that geocodes the addresses with mapquest via Google App Script. This works great, except it "recalculates" the addresses on page load. ...
2
votes
1answer
1k views

Report/process only using the last entry made by the user in Google Forms

I have a form where students can enter choices: for instance the specialisation they want to pursue, and the semester when they will start that specialisation. An example entry form is here, but it ...
7
votes
3answers
19k views

Google Spreadsheet generate random numbers with Math.random()

I'm trying to write a script function, which calls the Javascript Math.random() to generate a random number between [0, 1), like this: function f() { return Math.random(); } and I call it from a ...
7
votes
3answers
26k views

Duplicate Google Sheets' protected cells [duplicate]

I have created my spreadsheet with protected cells scattered throughout the document in Google Sheets. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep ...
5
votes
1answer
5k views

Custom script “invoked too many times” error

Credit: Google Spreadsheets function to find distance between cities function getDirection(city1, city2, unit) { var directions = Maps.newDirectionFinder() .setOrigin(city1).setDestination(...
4
votes
3answers
13k views

How to retrieve email address of current user?

In Google Spreadsheet I am having the following issue: I am using Session.getActiveUser().getUserLoginId() to fetch the current user's email id but other than creator of the spreadsheet it is ...
3
votes
1answer
7k views

Trigger Script in Spreadsheet in Similar Fashion to a Link

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a ...
3
votes
1answer
2k views

How to auto-update function based on background color [duplicate]

I've been referencing @w0lf's answer to help me calculate the sum of cells based on their background color in Google Sheets. From the comments and some other research it appears that changing the ...
2
votes
1answer
3k views

Replacing a spreadsheet formula with its result when the result satisfies a condition

In Google Sheets I'm setting up a sheet with a column of formulas referencing a cell in which there is an hourly rate. That hourly rate can occasionally change, but I don't want previous calculations ...
2
votes
3answers
3k views

Repeat range under each other in Google Spreadsheets

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this? So for example I have this range: Year | ...
1
vote
1answer
10k views

Date and time stamp automatically added in column [duplicate]

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
0
votes
2answers
671 views

Convert the data from comma-separated cells into repeated rows

I have a Google Spreadsheet where the first column is multi-valued (not all rows though) and also a few other columns. Not all columns are multi-valued. The values are separated by ',' Here is an ...
6
votes
5answers
23k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
5
votes
1answer
3k views

Using setBackgroundRGB() over setBackgroundColors()

I have a GSheet that is using IMPORTRANGE() to import data from some other sheet. On this destination sheet I want to color each row based on the value of column A. Because the source dataset may ...
5
votes
1answer
19k 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 ...
4
votes
2answers
17k 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 ...
4
votes
2answers
9k views

How do I refresh script results when a cell color is changed?

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. ...
3
votes
2answers
4k views

onOpen doesn't execute

I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs function onOpen(e) { var email = (Session.getEffectiveUser()....
3
votes
3answers
3k views

How can I open multiple URLs in Sheets using one cell?

I have been frequently using concatenate to combine cells and strings to render URLs in cells. The purpose of these URLs is to open specific pages to review the information on those pages. I am ...
2
votes
1answer
3k views

Remove Protection from Range

I have created 50 spreadsheets for 50 students. Each spreadsheet contains 33 sheets, numbered 1 to 33, along with a couple of other sheets. Within each of these 33 sheets, there are many protected ...
2
votes
1answer
1k views

How do I use a negative DURATION formatted cell from Google Spreadsheets in Google Scripts?

I'm using Google Spreadsheets and I have a sheet in the following format: Start | End | Partial result | Expected | Total 08:00 | 12:00 | 04:00 | 05:00 | -01:00 14:00 | 18:00 | 04:...
2
votes
1answer
2k views

Is there anyway to call an IMPORTRANGE() and have it include all sheets?

Here is my current function that combines the current data with the first archive. I'd like to set it up as a failsafe for the future so when there's a new archive it will be included in the import: ...
2
votes
1answer
1k views

Prevent “invoked too many times per second” error in Google Apps Script

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...
2
votes
2answers
171 views

Prevent “invoked too many times per second” error in Google Apps script

I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range ...
2
votes
2answers
640 views

How can I apply a script to every sheet but the first one on a spreadsheet?

I've been working on a shared Google spreadsheet on Google Drive. The document has different sheets with a "Last Modified Date" Cell. I found an script for this to be auto-filled searching here: ...
1
vote
1answer
4k 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....
1
vote
1answer
34 views

can't locate ; syntax error [duplicate]

I'm writing a function to calculate the income tax burden in the UK, and the Google Apps Script page keeps giving me an error notice that there's a missing semicolon (Missing ; before statement. (line ...
1
vote
1answer
5k views

Send a Google Forms response to specific email address based on form response

I need to send Google Forms responses to specific clinicians at my practice depending on whether they have been selected on a drop-down menu in the form. At the moment, Google Forms sends an email to ...
1
vote
2answers
9k views

Timestamp code for Google Sheets

I am using this script in order to put a time stamp in column B when someone edits in a row. However, I need it to place the time stamp if someone edits a row in column A. I don't need it to place a ...
1
vote
1answer
2k views

Copy and paste information once a day form a Google spreadsheet

I have a Google spreadsheet that is being modified during the day, and need to copy and paste the final information (at 8 pm) in a different tab, each day, pasting the new information under the old ...
1
vote
1answer
3k views

Is there any way to identify a user in Google Apps Script?

I have a spreadsheet in Google Sheets that is shared with five people. Only a few columns are edited; all of the other columns are locked. We need the ability to track changes to these editable ...
1
vote
1answer
2k views

Sync two cells value

Is there a way to way to link two cells so when one is altered the other is altered also? Like this: I alter `A1` to 1, `B1` is now 1 I alter `B1` to -1, `A1` is now -1
1
vote
1answer
3k views

How to duplicate a Google Sheet template sheet dynamically, preserving the protected cells permissions

I have two Google Sheet script functions that both work independently: 1) duplicates a template sheet, populating each duplicate sheet from a separate row of data in a source range; 2) makes a single ...
0
votes
1answer
1k views

How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?

I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's ...