RE: [Apps-Script] Return position of nth non-zero value in range
cbmserv...@gmail.com
Hi Aaron,
Apps Script code needs to access the spreadsheet data first before looking at the data to determine which nth item is 0.
Do you have other code that goes with this to make it work? Otherwise, you will need to add to it to make this work correctly.
From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Aaron Boyd
Sent: Friday, April 28, 2023 10:52 AM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: [Apps-Script] Return position of nth non-zero value in range
I have been trying to come up with a way to return the relative position of the nth non-zero value in a row range within Google Sheets. I have asked this question on Reddit too (https://www.reddit.com/r/sheets/comments/130v500/how_to_find_the_position_of_nth_nonzero_value_in/), and a helpful person provided a script to create a corresponding function to do this; however, I cannot get this to work when copying it over to Apps Script.
What I was provided was:
Returns "nth" non-zero value position, in "range"(single row or column)
NTH_NZPOS(range,nth)
=let(s,tocol(range),
iferror(
index(
reduce(,sequence(rows(s)),
lambda(a,i,if(ne(index(s,i,1),0),if(isblank(a),{i},{a;i}),a))),
nth,1),
"")
)
Which I have tried to put into Apps Script as below, which results in several errors.
function NTH_NZPOS(range,nth)
{
return =let(s,tocol(range),
iferror(
index(
reduce(,sequence(rows(s)),
lambda(a,i,if(ne(index(s,i,1),0),if(isblank(a),{i},{a;i}),a))),
nth,1),
"")
);
}
I am completely lost for how to adjust this code to work. Any solution that returns the relative position of the Nth non-zero value would be great, it doesn't have to involve a custom function (ideally working within the pre-built functions that come with Sheets would be ideal)
Thank you!
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6a3ac4f3-856f-44e9-8a32-eac7052c87a2n%40googlegroups.com.
cbmserv...@gmail.com
I don’t see any Apps Script code in this spreadsheet. Can you add in the Apps Script code so that we can see what you have put in and what you are missing?
From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Aaron Boyd
Sent: Friday, April 28, 2023 12:20 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Return position of nth non-zero value in range
Hi,
An example spreadsheet can be found here: https://docs.google.com/spreadsheets/d/1sVr3toqhrVEPReRsG0E5iYbLy6lcu23j5tz9-RBQzL0/edit#gid=0
What I'd like to do is detect when the "Dayn_reproductiveeffort" columns in each row have a non-zero value, and have the output be a number matching what day n is in that specific column header. The full spreadsheet has repeating patterns of columns A-Q spanning across many weeks, which I'm trying to summarize in a pattern of columns as seen in T-AB.
I've gotten some formulas to nearly work, but they all fall apart if there's duplicate values appearing in the selected columns. These are in columns S & X.
I don't have any other code involved here, just trying to find a way to make this work either with the built in functions of Google Sheets (preferred), or with the code snipped above that was provided by a Reddit user trying to come up with a solution.
Thanks!
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4546838a-e45f-4c20-bdc4-ce19ce538033n%40googlegroups.com.