Skip to main content

Questions tagged [google-sheets-custom-function]

Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.

Filter by
Sorted by
Tagged with
69 votes
13 answers
103k 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
  • 31.2k
32 votes
4 answers
19k 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.
Robin Alexander's user avatar
20 votes
3 answers
51k views

How to import JSON data into a Google spreadsheet?

Google Sheets has an IMPORTXML function to import data from various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there a similar way to import JSON formatted ...
kenorb's user avatar
  • 4,462
14 votes
2 answers
20k views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
Saul's user avatar
  • 141
13 votes
2 answers
62k views

Calculating shaded cells

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
salgire's user avatar
  • 131
13 votes
1 answer
18k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
Number8's user avatar
  • 231
12 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
  • 259
8 votes
2 answers
29k views

Google Sheets Filter Multi Column List With Another List Using A Function

I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C". List "A" has multiple records for each student ID number. List "B"...
Mr. B's user avatar
  • 2,877
8 votes
1 answer
542 views

Non-deterministic parameters to custom function

I have a problem with Google Spreadsheets: I create a custom function using the Script Editor and it only runs OK if the parameters I pass are known (constant) values. If the parameters are non-...
Cristian Lupascu's user avatar
7 votes
7 answers
41k views

LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
emihir0's user avatar
  • 187
7 votes
1 answer
1k views

Test Google Spreadsheets Add-On with custom functions

I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions ...
Eric Spreen's user avatar
6 votes
5 answers
18k 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
  • 61
6 votes
1 answer
8k 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(...
Linden Burger's user avatar
6 votes
1 answer
2k views

How do I reference existing formulas from custom formulas in Google Spreadsheet Scripts?

I want something essentially like this: function contains(arg1, arg2) { return NOT(ISERROR(search(arg1, arg2))); } But NOT and ISERROR are undefined even though using =ISERROR(...) works in the ...
Daniel X Moore's user avatar
5 votes
3 answers
7k views

How to avoid "invoked too many times per second" error

I made a Google Apps Script, but often I get this error: Script invoked too many times per second in this Google user account. This is the script: function papersControl(tipo, col, prev_cell, quant) ...
Pythonista's Apprentice's user avatar
4 votes
2 answers
59k views

How to set up delays in Google Apps Script? [closed]

I'm using the following method to encode URLs in Google Sheets: How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets? It works. The only problem is – when I apply it to a ...
Egor Driagin's user avatar
4 votes
1 answer
12k views

How to grant permission to self-defined functions?

I have in Google Sheets a table with the name of a contact in column 1 and want to retrieve their email address in column two. This is the code I have used: function email(name) { var contacts = ...
Gabriel's user avatar
  • 173
3 votes
1 answer
8k views

How to use custom function with arrayformula?

I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this? Let's say I have wrote a function that will return ...
Riccardo's user avatar
  • 589
3 votes
2 answers
2k views

Convert columns of ISO 8601 strings to dates in Google Sheets

I'm pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example: 2020-06-02T00:00:00Z ...
Julius's user avatar
  • 243
3 votes
3 answers
513 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
Eric's user avatar
  • 31
3 votes
1 answer
2k views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
tM --'s user avatar
  • 213
3 votes
2 answers
415 views

How to select range into a cell based on drop down selection

I have two sheets in my Google spreadsheet: Meal Plan and Recipes. Meal Plan looks like this: Recipes looks like this: I would like to write a formula or function which allows me to select a meal ...
Matt W's user avatar
  • 205
3 votes
2 answers
7k views

How to extract hovered links from a cell in Google Sheets?

I have set up this demo spreadsheet. Can someone please help me extract the URL?
orschiro's user avatar
  • 5,518
3 votes
1 answer
10k views

How can I solve error "Exceeded maximum execution time" in Google Sheets?

I found a script which I really need (link): function getTotalSum(cell) { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var sum = 0; for (var i = 0; i < sheets.length ...
user66322's user avatar
3 votes
2 answers
90 views

Filter function with multiple variables google sheets

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
Kyle's user avatar
  • 31
2 votes
2 answers
3k views

Google Sheets: Remove column headers from a QUERY()

I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row. =QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&...
Mr. B's user avatar
  • 2,877
2 votes
1 answer
1k views

How to pass multiple comma separated values in a cell to a custom function?

Suppose, there are values such as 1,A, 2,B, 3,D in certain cells. My intention is to add 1,2,3 (LHS) separately and A,B,D (RHS) separately. To achieve that, I have written certain custom function in &...
iammilind's user avatar
  • 173
2 votes
1 answer
2k views

Using custom function in QUERY in Google Sheets results in "unknown function:"

I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it ...
user9105932's user avatar
2 votes
3 answers
8k 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. ...
Justin Dearing's user avatar
2 votes
2 answers
42k views

How to format the time in this Google Apps Script

I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later. The cell's time is 20:00:00 but the output is 20:25. I am using this var formattedDate = Utilities....
Vais's user avatar
  • 21
2 votes
1 answer
3k views

Getting values from other sheets with a custom function

I've defined the following function, and use it in sheet1: function myfcn (sheetname,rowstart,rows,columnstart,rows){ var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); ...
DocDJ's user avatar
  • 21
2 votes
1 answer
2k views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into #...
seegoon's user avatar
  • 121
2 votes
1 answer
462 views

Google Sheets Passing Range from separate sheet into custom function

I have the following custom function I've built to basically reproduce a needle haystack type of functionality function ListBy (Range, Range2, needle) { var holder = []; if(typeof Range != '...
richbai90's user avatar
  • 123
2 votes
1 answer
524 views

Format cell, If word from list in cell

I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail. Imagine I ...
Jonathan Rolfsen's user avatar
2 votes
2 answers
1k views

ARRAYFORMULA with IF, DETECTLANGUAGE and LEN

I'm trying to make an array formula to detect the number of English characters in a range. When I use the following for individual cells: =IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
Jon's user avatar
  • 33
2 votes
1 answer
4k views

Is there any reliable alternative to IMPORTRANGE()?

After some time the formula breaks, and shows Error: Loading data..., and it won't work until I change anything in the parameters, e.g. Sheet1!a3:Ag to Sheet1!a3:AG, then it works for a few days, and ...
Gleb's user avatar
  • 313
2 votes
1 answer
5k views

How to send cell as argument for function?

I am trying to write a custom function for Google spreadsheets that checks the formatting of a given cell and acts accordingly. I know that I can give a string as argument and get it to work but then ...
Kajsa's user avatar
  • 121
2 votes
1 answer
2k 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 ...
DanielAttard's user avatar
2 votes
1 answer
2k views

3D Formula Google Sheets - sum across sheets

I'm trying to script an equivalent to Excel 3D formula into Google sheets. I have a workbook with numerous worksheets and a master sheet that should sum up the data. In excel I would have used this 3D ...
Mady's user avatar
  • 21
1 vote
2 answers
313 views

Google sheets range of sheets? [closed]

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
Him's user avatar
  • 123
1 vote
2 answers
4k views

Multiple IF statements: IF, AND, ISDATE, THEN formula

I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
que syrah sarah's user avatar
1 vote
1 answer
3k views

Conditional Formatting COUNTIFS (Google Sheets)

I'd like column P to be highlighted when: A contains "x" E contains "approved" O < today (date has passed) P and Q are blank My formula doesn't work: =COUNTIFS(A6:A, "x", E6:E, "approved", ...
que syrah sarah's user avatar
1 vote
1 answer
4k views

Google Sheets casting cell value from Object to Number

One cell in a Google Sheets document holds a value given by a SUM of cells formatted as "elapsed hours":"elapsed minutes". Attempting to make calculations in a custom script on this cell will throw ...
Riccardo's user avatar
  • 589
1 vote
1 answer
2k views

All columns values in one column

I need some help! :S I have a spreadsheet and three list of items (Column "A", "B" and "C") and I need to put all the values of all the columns in only one column (Column ...
Axel Frey Guarenas's user avatar
1 vote
1 answer
266 views

How to return a Lambda Function through a Custom Function?

I know there are alternatives to this. You can simply pass the variables you want to pass to the custom function. But that's not what I want to happen. Something like: =CUSTOMFUNCTION(A2,A3)(A4,A5) ...
David Tan's user avatar
  • 1,025
1 vote
1 answer
30 views

Cell formula which returns the number of sheets

I'm trying to add some "validation" to a google sheet to prevent errors from creeping in as the sheet is edited (think assertion when programming). Because of the way my sheet is structured ...
Att Righ's user avatar
  • 194
1 vote
2 answers
2k views

In Google Spreadsheet run a function from cell programmatically

I made an Apps Script custom function in a Google spreadsheet. I can call it from a cell and works fine (In Google Script, get the spreadsheet cell calling a custom function). The function makes an ...
Rodrigo E. Principe's user avatar
1 vote
1 answer
558 views

Google sheets, how do I combine data from two cells, where there is a URL (web-link) in the cells, without loosing the link(url)

I have tried to write a custom function in google sheets. I tried to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (...
ctrl-alt-delor's user avatar
1 vote
1 answer
542 views

In Google Sheets,how to get a value lower than a particular cell value in a row?

In Google Sheets, I have numbers in 12 cells in a row. Then in the 13th cell of the same row, I have a number which is one of these 12 numbers. Now, I want find the 1st value lower than the value in ...
alerts4 deepak's user avatar
1 vote
1 answer
334 views

Is there a way to do a "filter", "if" function across multiple columns to only pull information based on certain criteria and headers?

I do not know how to explain this perfectly without a picture so please take a look at the image before reading. I am trying to filter the data in Fig 1 (Above). based on the headers and then if the ...
Lina's user avatar
  • 25