Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
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 ...
Rubén - Volunteer Moderator -'s user avatar
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))
ttarchala's user avatar
  • 1,679
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 ...
Ian Viney's user avatar
  • 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 ...
Rubén - Volunteer Moderator -'s user avatar
7 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), )...
user0's user avatar
  • 26.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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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) )
SimplGy's user avatar
  • 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 ...
SauloAlessandre's user avatar
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.
Donald Love's user avatar
4 votes

How to import JSON data into a Google spreadsheet?

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 ...
kingkool68's user avatar
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)))
Markus Frömmel's user avatar
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 ...
Rui Fonseca's user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
marikamitsos's user avatar
  • 12.8k
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 ...
Vidar S. Ramdal's user avatar
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 ...
doubleunary's user avatar
  • 14.7k
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 ...
JohnSUN's user avatar
  • 871
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:...
HardScale's user avatar
  • 134
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 ...
Erik Tyler's user avatar
  • 5,048
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
Aldric Bocquet's user avatar
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, "")
user0's user avatar
  • 26.6k
2 votes
Accepted

How to reference a Google Sheet by filename?

Google Sheets hasn't a built-in function to make reference a Google Sheet by filename and a custom function can't be used because they ran anonymously and the Google Apps Script Google Drive and ...
Rubén - Volunteer Moderator -'s user avatar
2 votes

LEFT JOIN two and more tables in Google Sheets

Let's say your Table A were in A1:C, and your Table B were in E1:G. The following array formula would generate the table you indicate (placed anywhere other than Columns A:G): =ArrayFormula(QUERY({...
Erik Tyler's user avatar
  • 5,048
2 votes
Accepted

Convert columns of ISO 8601 strings to dates in Google Sheets

The IMPORTJSON project include ImportJSONAdvanced. This function can't be used directly in a formula but could be used to build a custom function. The key part is the parameter transformFunc. It ...
Rubén - Volunteer Moderator -'s user avatar
2 votes

Google sheets range of sheets?

Another way to do this is to use the INDIRECT statement. You will still need to list the sheet names, but if they are sequential or if you have a list with the names, you can copy and paste them into ...
Jeromef's user avatar
  • 21

Only top scored, non community-wiki answers of a minimum length are eligible