30

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 ...


17

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/...


9

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

To @Jacob's claim of impossibility, I refute it thus... (but thanks for the improved speed) using: =sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1)) with the following functions will do what you want. /** * Sums cell values in a range if they have the given background color * * @param {String} color Hex string of color eg ("#ffffff") * @...


6

I've made some research and it seems like an old problem, even from old MS Excel versions. I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom()) function plusOne(i) { return i + 1; } function myRandom() { # Some function return ...


4

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


4

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("...


4

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


4

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(), ...


3

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.


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

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 ...


2

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} * @...


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

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: "Test as add-on" from standalone project to Google Sheets doesn't link ...


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

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 ...


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

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 ranges. BUT. Make sure all of the ranges have the same amount of rows. If not, it will just be messy :) Step 2. Create your helper columns somewhere on the same ...


2

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 could be a JavaScript function that transform the ISO 8601 dates into JavaScript Date objects which could be transferred to the spreadsheet as dates. This might ...


1

From Migrate to the New Google Sheets Custom function arguments must be deterministic Built-in spreadsheet functions that return a different result each time they calculate — such as NOW() or RAND() — are not allowed as arguments to a custom function in the new version of Sheets. If a custom function tries to return a value based on a volatile ...


1

I would go about doing it like so. Code function myValues(range) { var output = []; for(var i = 0, iLen = range.length; i < iLen; i++) { output.push(checkValue(range[i][0])); } return output; } function checkValue(value) { var check; if(value >= 1 && value <= 71) { check = 1; } else if (value == 72) { check = '...


1

When I try to apply this formula to any row after the first row (excluding headers) I get a #NAME? error The#NAME? error occurs on custom functions when the custom functions use a service, in this case SpreadsheetApp, that requires authorization to run and it is not authorized yet. To authorize your script, Go to the script editor by clicking on Tools > ...


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