31

So I've searched long and hard for a good answer to this and here is what I have found: an unmodified range parameter passes in the values of cells in the range, not the range itself (as Gergely explained) ex: when you do this =myFunction(a1:a2) to use a range in your function you need to first pass the range in as a string (ex: =myFunction("a1:a2") ), then ...


18

Currently there is no any Add-on which offer this functionality, so you have to create or import custom functions by Script Editor available in Tools menu (for Spreadsheet). Then re-paste the content of ImportJSON.gs from bradjasper's GitHub page and save it. After that, you can use ImportJSON function, for example: =ImportJSON("http://gdata.youtube.com/...


8

The answer to your first question is: Code function test() { return SpreadsheetApp.getActiveSheet().getActiveCell().getColumn(); } Screenshot Remark Add the code under Tools>Script editor and press the save button. Example I've prepared an example file for you: sample GAS code


8

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 and return a single value. Please checkout https://developers.google.com/apps-script/guides/sheets/functions. It shows how to write a simple function, DOUBLE(), ...


7

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))


6

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 your custom function call GetParamRanges() like this, passing it your function name: function CustomFunc(ref1, ref2) { var ranges = GetParamRanges("...


6

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 calling the function, like assign the script to a custom menu, an image, an installable trigger or by running it from the Script Editor.


5

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 if we have a sheet which ID is 503917557, then the resulting formula is =SheetName("503917557") if the name of the sheet with ID = 503917557 is changed, the ...


4

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 to rows you can use http://www.convertcsv.com/transpose-csv.htm I found this easier than messing with an addon script.


4

Please try: =len(A1)>9 applied to a range starting with A1.


4

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 E1:G5 data in one sheet. All you need is only put a line like that: =LEFTJOIN("A1:C5";"E1:G5"; "A=F";0) We can see a working example here I used code from ...


3

You may not need a script for this as a formula of the kind: =iferror(vlookup(U1,D$1:E$20,2),"Error: No box location!") copied down to suit may serve where D1:E20 is a table of the kind: This could be in the same sheet but seems sensible to hold such 'reference' data in a separate sheet (in which case the formula above would require the addition ...


3

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 range: /** * @param {string} color String as background color to be searched for in sumRange * @param {range} sumRange Range to be evaluated * @return {number} * @...


3

As an alternative, inspired by the comment from @Lipis, you can call your function with the row/column coordinates as additional parameters: =myFunction(B1:C2; ROW(B1); COLUMN(B1); ROW(C2); COLUMN(C2)) In this form, the formula can easily be copied (or dragged) into other cells, and the cell/range references will be automatically adjusted. Your Script ...


3

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), ))} if you want it to export into Sheet3 use: ={{QUERY(Sheet1!A1:C, "select *", 1)}, {"material", "required", "total"; ARRAYFORMULA(IFERROR(VLOOKUP(Sheet1!...


3

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 equivalent to =GetNumber('1C'); If you are somewhat familiar with JavaScript, you could instead do the split operation within your function code: function ...


2

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:z500)) Name the sheet "Ref". Then when you need a reference to a cell as a string instead of the contents, you use: = some_new_function('Ref'!C45) Of course, ...


2

You added single quotes to the sheet references: =ListBy('sheet1'!A1:A10, 'sheet1'!A1:A10, "needle") If you leave those: =ListBy(Sheet1!A1:A10, Sheet1!A1:A10, "needle") then your script works as planned. Screenshot Sheet2: Sheet1:


2

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-on it's required to include at least one add-on menu, but this is not working at this time. Remarks There are a couple of related GAS issues, please star them: ...


2

@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)))


2

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 object. Explanation Doing arithmetic operations When we are doing arithmetic operations on dates/times/duration, we should bear in mind the following: Google ...


2

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 again and the result updates. Still manual, but probably better than refreshing the page.


2

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 involve any calculations that don't work with arrays, but as written, these formulas would work: In E1: =ArrayFormula({"Custom Column 1";IF(A2:A="","",Function(A2:...


2

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, "")


2

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 Google Drive Advanced services require authorization to use them. The only way is by using scripts triggered by other means like a macro, a custom menu, dialog or ...


2

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 very clear, but regarding this we can only give feedback to Google. For this use Google Feedback. The solution is to replace your sheetnames custom function ...


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