Extract RowID from page path in Google sheet

[Katelyn_Alberts]

I know there is a way to extract the rowID from a page path in Glide, but I need to know how to do it in the Sheets.

I need to extract what comes after the last /

Is there a built-in solution in sheets or do I need a formula?

[Jeff_Hager]

This might be a useful read. There’s a few ways to do it. I would suggest trying to split on '/r/’ and grab the second element.

Get first item from Split()

[Katelyn_Alberts]

Hey Jeff thanks for sharing this, I was kind of able to follow the general concept of what they are doing but I don’t know anything about code or how to transition it to make sense for my app. SO I’m wondering if I can do it through Glide instead and get the end of the path to show up on Google Sheets.

I extracted the path here on Glide, but is there a way to get that extracted text into regular text so that way it will appear in my sheet?

[Jeff_Hager]

Yeah, you can do it through glide, but to get it into the google sheet, you would need to perform a set column action somewhere, to write the computed values to a basic column. That action can only be called by a user. Probably more work in the end and unnecessary updates. I would still recommend doing it in the google sheet if it absolutely needs to be directly in the sheet.

I had a play with it. I tried 3 different versions from that post and they all worked, but kind of split weird in google sheets. Seems the split in sheets doesn’t work great if the split string is more than one character. It split on '/’ instead of '/r/’. I think I found something better. Place this in the first row of an empty column in your sheet.

={"Column Name"; arrayformula(ifError(REGEXEXTRACT(A2:A,"\/r/(.*)")))}

[Katelyn_Alberts]

Hi Jeff, thanks so much for helping.

quick add: I need to do it on the sheets bc I need to make a pivot table of condensed rowIDs to get averages and rankings

I now understand where the formula goes. It looks like the person who was helping me before had a similar formula to yours, as seen on this picture. I named yours Jeff Path.

My original problem was that the “path end” stopped working as new rows/actions were coming through and the K column was just empty on the last 50 rows.

The formula you gave me works on a bunch of them but others not as seen here:

As you can see here, your J = Jeff column goes all the way to the bottom but the K column is empty and not acknowledging new rows.

Is it normal for the formula columns to not update and capture new data? Should i use the previous formula and keep manually entering it to update it?

[Jeff_Hager]

@Katelyn_Alberts

My original problem was that the “path end” stopped working as new rows/actions were coming through and the K column was just empty on the last 50 rows.

@Katelyn_Alberts

As you can see here, your J = Jeff column goes all the way to the bottom but the K column is empty and not acknowledging new rows.

Looks like your previous formula is not an array formula. That means you would have to place that formula in every single row. Whoever helped you before only pre-populated so many rows, and when your data expand past that, those new rows didn’t have a formula and remained empty. On the other hand, my formula is an array formula, so you only need to add it once and it will populate the same formula in every row automatically.

@Katelyn_Alberts

The formula you gave me works on a bunch of them but others not as seen here:

It looks like your old formula is possibly looking for the last ’/’ and returning everything afterwards, whereas mine is looking for the first '/r/’ and returning everything afterwards, even if there is a second ‘/r/’ path.

The old formula doesn’t consider if the final part of the path is a RowID. My formula does, but doesn’t consider if there are more than one ’/r/’ in the path. So in my eyes, neither is entirely correct, but if you are happy with the old formula, you can change it to an array formula.

I’m not very good at RegEx to fully understand it’s syntax, but try this and see if it works for you. It’s your old formula, but wrapped in an array formula. If you want to replace the existing K column, then you’ll have to wipe out that formula on every subsequent row.

={"path end"; arrayformula(ifError(REGEXEXTRACT(A2:A, "[^/]+$")))}

[Katelyn_Alberts]

Jeff I think you nailed it!!! It’s looking perfect right now, I’ll continue to watch how the data rolls in, but this seems great - thank you soo much!!!

[Darren_Murphy]

@Katelyn_Alberts

quick add: I need to do it on the sheets bc I need to make a pivot table of condensed rowIDs to get averages and rankings

I’ve never understood Pivot Tables, but aren’t they essentially just relations and rollups?
Can’t help thinking that you’re going to an awful lot of trouble to do something in your Google Sheet that could very easily be done in Glide

[Katelyn_Alberts]

In this case, I’m not sure it would work bc there are so many duplicate rows. So if one business has 5 rows all being considered said business then the relations and rollups would be multiplied by 5 rows, no?

on my sheets, the pivot table reduced 1100 rows down to 470 rows so that each business only had 1 row.

Clearly…I don’t know what I’m talking about most of the time though so you totally may be right lol

[Darren_Murphy]

I would have thought the equivalent in Glide would have been:

• A multiple relation column in your Businesses table that matches the RowID with the RowID in your GA Data table
• One or more rollups via that relation to sum the column data

No?

(again, I don’t really understand Pivot Tables, so maybe there is something else they give you that I don’t know about)

[Jeff_Hager]

Nobody understands Pivot Tables. Except my boss. He is obsessed with them…

[Katelyn_Alberts]