10
votes
Accepted
How to use custom function with arrayformula?
In order to use a custom function with array formula, it should be able to return to get as input and return as result array of values.
The custom function provided as example has no input parameter ...
- 44.6k
9
votes
Accepted
Google Sheets Filter Multi Column List With Another List Using A Function
Use a FILTER/MATCH combination. Example on your data below:
=filter(C52:F57; match(C52:C57; I53:I54; 0))
- 1,649
9
votes
How to pass a range into a custom function in Google Spreadsheets?
I have extended the excellent idea in user79865's answer, to make it work for more cases and with multiple arguments being passed to the custom function.
To use it, copy the code below, and then in ...
- 191
7
votes
Accepted
How to grant permission to self-defined functions?
Custom functions can't call services that require authorization. Source: https://developers.google.com/apps-script/guides/sheets/functions.
The way to overcome the problem is to use another way for ...
- 44.6k
6
votes
How to set up delays in Google Apps Script?
To set up a delay on the execution of a Google Apps Script use the method sleep(milliseconds) of Class Utilities.
I'm intentionaly not including an example of using this method here because this ...
- 44.6k
6
votes
How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?
For other's reference - a similar method also works for decode.
function decode(value) {
return decodeURIComponent(value);
}
- 103
6
votes
LEFT JOIN two and more tables in Google Sheets
one-cell solution (paste in D1):
={"material", "required", "total";
ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )),
ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), )...
- 26.2k
5
votes
How to make that a custom function update its result immediately
Contrary as occurs with built-in functions like =NOW() that return an new value every time that the spreadsheet is recalculated, custom functions are recalculated only when their arguments change. So ...
- 44.6k
5
votes
How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?
You can do this without a custom function using ENCODEURL.
eg:
=hyperlink(
ENCODEURL(A1)
)
- 151
5
votes
LEFT JOIN two and more tables in Google Sheets
Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy.
Supose you have all in A1:C5 and ...
- 151
4
votes
How to import JSON data into Google Spreadsheets?
If you want to paste a JSON object you can use http://www.convertcsv.com/json-to-csv.htm to convert it to a CSV and then import that into Google Sheets.
If you want to rotate the data from columns ...
- 141
4
votes
How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?
For others who are still searching for this, Google Sheets has a function called ENCODEURL now (Aug 2018), so you don't need to make your own any more.
- 41
3
votes
Google Sheets Filter Multi Column List With Another List Using A Function
@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this:
=filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))
3
votes
How to make that a custom function update its result immediately
You create a tick box, for example at cell C1, and then set your target cell as:
=if(C1=true, SheetName("123"),)
When you untoggle and re-toggle the tick box, the function will be called ...
- 31
3
votes
Pass range in Google Apps script custom function without using A1 notation
Reference: http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html
Instead of passing parameters as string to the custom function, following script takes input as a ...
- 151
3
votes
Using custom function in QUERY in Google Sheets results in "unknown function:"
The main problem is that sheetnames function returns a string while the QUERY's first argument should be a range reference or an array of values.
Another problem, is that Google Sheets errors aren't ...
- 44.6k
3
votes
Convert columns of ISO 8601 strings to dates in Google Sheets
I believe you can simplify the whole procedure by slightly tweaking your already existing formulas and using helper columns.
Please follow these steps:
Step 1.
You can use either open or closed ...
- 12.6k
3
votes
Accepted
How to pass multiple comma separated values in a cell to a custom function?
Part of your problem is that SPLIT, when used as an argument to a function, returns a string. Say your C1 cell contains the text 1,C. Your formula
=GetNumber(SPLIT(C1, ","))
is actually ...
- 17.1k
3
votes
Accepted
Google Sheets: Remove column headers from a QUERY()
To remove headers, you should refer to the data range A2:G rather than the full columns A:G, and explicitly set the headers parameter to zero.
Additionally, you need to use the label clause to omit ...
- 13.2k
3
votes
Accepted
How to select range into a cell based on drop down selection
Select all cells of the Ingredients column from the Recipes sheet using the FILTER() function by the condition "the Meal column is exactly equal to the selected value in cell C4". Combine ...
- 819
2
votes
How to pass a range into a custom function in Google Spreadsheets?
I was working on this a few months ago and came up with a very simple kludge: create a new sheet with the name of each cell as its contents:
Cell A1 could look like:
= arrayformula(cell("address",a1:...
- 134
2
votes
Test Google Spreadsheets Add-On with custom functions
NOTE: This answer was cross-posted to Stack Overflow. I have made some updates there.
Answer
According to Eric Koleda in Code.gs - date_add_and_subtract to make available the custom fuctions in an add-...
- 44.6k
2
votes
Accepted
Google Sheets casting cell value from Object to Number
Short answer
How can I convert a cell value to a number?
On Google Sheets durations are just a display format for date values, so when a custom function takes a duration it's treated as a date ...
- 44.6k
2
votes
How to get new Google Form responses to take on non-form functions in Google Sheets
This post is eight months old as I respond here, but the basic answer is, Yes: use an array formula in the first cell of those columns.
I don't know what your custom functions do or if they would ...
- 4,980
2
votes
Accepted
Conditional Formatting COUNTIFS (Google Sheets)
try this one by entering it into desired cell at 6th row and expanding the range (eg. P6:P):
=COUNTIFS(A6, "x", E6, "approved", O6, "<"&today(), P6, "", Q6, "")
- 26.2k
2
votes
How to set up delays in Google Apps Script?
Not the most elegant solution but I used the script cache as a mutex of sort to prevent too much executions in parallel. I'm sure some must execute at the same time but few enough to not be a problem ...
Only top scored, non community-wiki answers of a minimum length are eligible