Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
75
questions
56
votes
12answers
73k 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:
...
19
votes
2answers
45k 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 ...
12
votes
2answers
56k 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 ...
11
votes
3answers
12k 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 ...
9
votes
1answer
12k 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)
9
votes
1answer
14k 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 ...
8
votes
1answer
471 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-...
7
votes
1answer
978 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 ...
5
votes
1answer
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 ...
4
votes
4answers
19k 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....
3
votes
2answers
18k 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"...
3
votes
1answer
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 ...
3
votes
1answer
8k 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 ...
2
votes
2answers
309 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
...
2
votes
3answers
446 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 ...
2
votes
3answers
5k 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
2answers
25k 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....
2
votes
1answer
2k 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);
...
2
votes
1answer
1k 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
#...
2
votes
1answer
237 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 != '...
2
votes
1answer
48 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 ...
2
votes
2answers
596 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(...
2
votes
1answer
3k 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 ...
2
votes
1answer
3k 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 ...
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
1answer
36 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 ...
1
vote
1answer
4k 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 ...
1
vote
2answers
4k 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?
1
vote
2answers
3k 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 ...
1
vote
1answer
2k 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", ...
1
vote
1answer
3k 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 ...
1
vote
1answer
28 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 ...
1
vote
1answer
47 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 ...
1
vote
1answer
50 views
Friendly Team vs. Enemy attack math and complicated functions in Google Sheets
How would I go about writing a function in Google Sheets?
If Friendly Team Hero Power P2,P4,P6,p8 etc. is greater then Enemy Team Power "T2:T15" Else Don't match it and instead look for titan, Same ...
1
vote
1answer
283 views
Problem with COUNTIF in Google Sheets Data Validation Custom Formula
I am following some tutorials and discussion on forcing uniqueness in Google Sheet Column (I need it for ID indexing). I have been using Data Validation for some time, but i have a problem with custom ...
1
vote
1answer
249 views
How to pass an entire sheet as a parameter to a Google Sheets function with dependencies
I have a Google Sheets app script that is passed the name of a sheet. Internally it pulls the data from that sheet and returns a value. Something like this:
function myFunction1(partNumber) {
var ...
1
vote
1answer
607 views
Google Sheets - How to count the occurrence of each day of the week if they are not public holidays?
I would like to count the occurrence of each day of the week in a month if they are not national holidays.
In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and ...
1
vote
1answer
102 views
ArrayFormula stops working after shuffling rows
I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets.
The problem I have is when I need to rearrange rows then what ...
1
vote
1answer
1k 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 ...
1
vote
0answers
17 views
Function(“url”) With A1 Cell Contents as Part of URL Query
I am trying to import JSON data into Google Sheets essentially like IMPORTHTML("URL"&A1&"URL"). Basically trying to adjust the URL by the contents of Cell A1. It seems to ...
1
vote
1answer
41 views
Isolating conditional formatting to each range in a column
I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows ...
1
vote
0answers
32 views
In Google Apps Script, how do you get the Range of the calling cell in a custom function?
I'm trying to get a reference to the calling cell's range in a Google Sheets custom function.
For example:
function MyAddress() {
callingRange = // get calling cell reference here
return ...
1
vote
0answers
153 views
Custom function on Google Sheets that can extract hyperlinks from formulated cells
I recently found this post, which is so close to my solution, but not quite there!
Extract the link text and URL from a hyperlinked cell
The issue with this custom formula (=LinkURL()) is that it ...
1
vote
3answers
1k views
Using a script to only take only take cells with values in google sheets
tl;dr how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?
Background
I have an array in Google Sheets with date-ordered data (note ...
1
vote
1answer
252 views
How to do permutation of characters and print it horizontally along that row in Google Sheets?
I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are.
Is it possible? Because I searched it all over and only found vertical ...
1
vote
1answer
599 views
Scitpt to duplicate template sheet into new workbook, then rename
I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time.
...
1
vote
1answer
69 views
Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE
How to adapt this function to exclude a specific cell or how to apply just to one cell?
function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}
1
vote
1answer
132 views
Copy formula every other row and every column
I have been playing with scripts to try and figure out this tedious task as there are 600ish rows in my sheet, times 4 sheets.
So far I have come up with this
function myFunction() {
var ss =...
1
vote
2answers
7k views
Cell values do not update
I want to update every minute a cell with data from a HTML link. The problem is that the value does not update unless I manually empty the cell or make changes to the script. I tried to add NOW() to ...
1
vote
1answer
399 views
Custom Script in Pivot Table Value Formula = Error Loading Data forever
I am very new to the Google Sheets platform, but I am not new to programming, and am comfortable creating and managing javascript code, and reading API documentation.
I am working on a side project, ...
