Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
20

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


2

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

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


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


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

The workaround would be to temporarily disable ARRAYFORMULA by removing = sign or inserting ' in front of = sign - then do your row swapping and then again fix your array formula. The second workaround would be to delete the static value after you moved your row so the array formula could roll out instead of posting #REF! error. The 3rd option includes a ...


1

function SHEETLIST() { try { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() var out = new Array( sheets.length+1 ) ; out[0] = [ "NAME" , "#GID" ]; for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = [sheets[i-1].getName() , sheets[i-1].getSheetId() ]; return out } catch( err ) { return "#ERROR!" }} =QUERY(SHEETLIST(TODAY(...


1

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


1

or this indirect (shorter) one with a custom formula: =URL("A1") function URL(input) { var range = SpreadsheetApp.getActiveSheet().getRange(input); var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1]; return url; }


1

add this script to your spreadsheet and then use this custom formula: =URL(A1) function URL(reference) { var sheet = SpreadsheetApp.getActiveSheet(); var formula = SpreadsheetApp.getActiveRange().getFormula(); var args = formula.match(/=\w+\((.*)\)/i); try { var range = sheet.getRange(args[1]); } catch(e) { throw new Error(args[1] + ' is not a valid range');...


1

Hmm, based on the documentation for Detectlanguage, it doesn't look like you can feed it a range. The best it seems you can do is define it as an array formula and check each line you wish to check, but that won't really work for an arbitrarily large array. An idea I had was to use the app's service to translate everything to English and then compare ...


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

=IFERROR(IF(AND(P6="closed", Q6<"Today()"), Q6-B6, ""), "NA") can be slightly improved to: =IFERROR( IF( AND(P6 = "closed"; Q6 < "NOW()"); IF((Q6-B6) < 0; ""; Q6-B6); ...


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 distinguish two different custom functions in Google Spreadsheet, via Google Apps Script: One that calls for an API; SpreadsheetApp, (example) One that doesn't make any calls, (example) The first function is capable of doing almost anything. Apart from calling the Spreadsheet service, it can call upon the GmailApp or any service made available by Google. ...


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