21

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


6

When passing a Range to a Google spreadsheet function, the framework executes paramRange.getValues() implicitly and your function receives the values in the Range as a 2-dimensional array of strings, numbers or objects (like Date). The Range object is not passed to your custom spreadsheet function. The TYPEOF() function below will tell you what kind of data ...


5

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

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


4

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


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

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


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


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

There are several ways to change your script. You could use insertRows(nowIndex, numRows) among other methods.


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

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


1

The way that Sheets and Apps Script handle "dates" are very different. For Sheets the "date" unit is 1 day; The base date is 1899-12-30 0:00:00, getting the timezone from the spreadsheet settings. For Apps Script, which is based on JavaScript, the "date" unit is 1 millisecond. The base date is 1970-1-1 00:00:00 UTC. Below is an example script that if the ...


1

Custom function doesn't work on features like Pivot Table, conditional formatting, etc. They work only when applied directly as cell formulas. The workaround is to add an auxiliary column on the Pivot Table source data and extend the reference accordingly but it's highly recommend to optimize IMPORTJASON first in order to avoid having to repeat this custom ...


1

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


1

Solution #1 I think I figured it out! I just set the second condition to be a date less than today (since the dates in column Q will be the closure date, they are dates in the past). I modified the generic IF-AND formula: =IF(AND(P6="closed", Q6<"Today()"), Q6-B6, "") Solution #2 For the negative values, I used conditional formatting and made the ...


1

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


1

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


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


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

Better is to prepare a result for a range in stead of dealing with individual cell. When you open the file, it needs to process all the cells individually. This, in the end, evoked the error. Therefore I created the below code. Code function catExp_1(range) { var output = [], arr = JSON.parse(createArr()); for(var j=0, jLen=range.length; j<jLen; j++)...


1

I have tried the following custom function successfully up to 170 sheets: function getTotalSum(startrow, startcol) { var ss = SpreadsheetApp.getActive(); var start = new Date(); var numSheets = ss.getNumSheets(), sum=0; for(var k=1; k<numSheets; k++) { var data = ss.getSheets()[k].getDataRange().getValues(); var value = data[startrow-1]...


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