SyncNDBNMembers is pulling from an out of date page

[cielf]

Summary

Our pull-down list of NDBN members in the organization edit page is out of date because SyncNDBNMembers is pulling from an out of date page

Why fix?

If we're going to ask for this info, we need to provide the ability to pick it for newer members too!

Details

SyncNDBNMembers currently points at a page that is no longer updated: https://ndbn.memberclicks.net/member-ids

The page that we need to point to is this one: https://docs.google.com/spreadsheets/d/1DRXeG206M0kgtUYT9lpD8fyEWRO7-a6m/edit?usp=sharing&ouid=107461548380845807455&rtpof=true&sd=true

There will likely be some need to adjust the scraping to get the values from this page.

Further info

1/ Our contact at NDBN assures us that this is a more permanent link.
2/ This regards the support thread in human-essentials-support from January 11, 2023 at 7:21 pm

Criteria for completion

• [ ] ndbn members list updates correctly
• [ ] any existing tests modified to accomodate the change (I'm not sure there are any tests)

[sunsheeppoplar]

Looked at this issue a bit over the weekend @cielf and initially came to the conclusion that there doesn't seem to be an easy way to scrape Google Sheets with Nokogiri as the primary tool used prior. Would it be appropriate, you think, to have a service level account and just fetch it through the Google API? I'm a bit out of the Ruby scene and was looking around for other gems to interact with google sheets / drive. Maybe Google's own client?

[cielf]

@sunsheeppoplar I confess, I haven't thought about how to do this as yet.

[sunsheeppoplar]

I don't mind taking a crack at it if you'd like. If there's more of a fleshed out proposal needed, I don't mind putting that together for review either.

[cielf]

Assigning it to you, @sunsheeppoplar. My naive approach would probably be to see if I could download the csv and then work with it (reducing the problem to something easier than scraping) but I don't know what issues that would raise.

[sunsheeppoplar]

@cielf Thanks for your help so far. Just wanted to leave some more notes to see how we might be able to proceed. I looked into your idea some more and wasn't sure of a few things.

• this seems like it's being running as some kind of cron based on the original PR #2771
• if that's the case, it seemed like it would be extra steps to download the file onto the Heroku server and then reading it and then deleting it
  • additionally, since I don't have a ton of access to deployed environments, I wasn't totally sure if that was possible

Given my assumptions are true and this isn't a one time operation, I started looking into programmatically reading the Google Sheet through their API. Playing around on their sandbox environment. You can just have the API key select box selected in the following examples in the right pane.

• it's 400ing with a FAILED_PRECONDITION error
• looking around it seems to be known that if it's an Excel document uploaded into Google Drive / Sheets, then we can't call it programmatically

I made a public copy to test if it was in fact that issue

• looks like we get responses back

All of that said, can we ask the folks at NDBN to convert the link to something that we can work with? This is outlined above in the known issues link. Would that be dependable or would they likely continue to upload Excel documents and maybe forget to convert them into Google Sheet's native format? How often are they updating these lists?

Apologies for the long comment, just wanted to give you the context and what I've tried. I can also try making the next office hours or schedule some time other than that to talk with you / anyone else, if that's appropriate.

[cielf]

I'm not sure how often they are updating. Probably either whenever they get a new member or monthly?
Not to worry about the long comment -- it's good to see what you've done so far. I still haven't wrapped my head around it -- but maybe someone else on the team (e.g. @dorner) will have something of use to help you with this one.

[dorner]

@sunsheeppoplar thanks for your hard work on this! I think you're right and we'd likely need some kind of transformation or other source of information rather than Google sheet. @cielf I think the ball's in your court here. It sounds like it could be as simple as importing / exporting the sheet in a slightly different format. Alternatively we could have them export as CSV and provide them a place to upload it (maybe only for a superadmin). Either way I think we need more input from stakeholders as to our options.

[sunsheeppoplar]

@dorner thanks for your feedback, happy be helping again. Yeah, I mostly just don't know if they'll use the Google Sheet properly. Seems like it could work if they remember to import it / use it natively, but I don't know if that's totally reliable as they continue to update. Looking forward to how to solve it, thanks, y'all.

[cielf]

@sunsheeppoplar Haven't gotten to this yet - concentrating on investigating a serious issue this week.

[github-actions[bot]]

This issue has been inactive for 240 hours (10.00 days) and will be automatically unassigned after 120 more hours (5.00 days).

[cielf]

@sunsheepoplar -- Sorry still haven't gotten to it -- we're seeing some light at the end of the tunnel for the other issue though!

[github-actions[bot]]

This issue has been inactive for 262 hours (10.92 days) and will be automatically unassigned after 98 more hours (4.08 days).

[cielf]

I'll reach out to our contact at NDBN and find out if it's plausible for them to do it a different way / how often they update it, but I expect their processes are excel-based.

[github-actions[bot]]

This issue has been inactive for 262 hours (10.92 days) and will be automatically unassigned after 98 more hours (4.08 days).

[github-actions[bot]]

This issue has been inactive for 382 hours (15.92 days) and is past the limit of 360 hours (15.00 days) so is being unassigned.

[cielf]

The ball is, indeed, in my court. I like the idea of having a capability for them to upload a CSV. I'm not keen on giving them superadmin access, per se. @dorner - would adding a 'NDBN' role be a big deal -- this is probably the only thing it would do -- though I could see a possible case for them seeing the NDBN annual surveys for the banks that are NDBN members eventually. I'll loop in Judi on this.

[dorner]

Yes, we could do that - basically introduce a role that's more than an org_user but less than an org_admin. I'd try to see if there was some higher-level concept we could use here rather than literally calling it "NDBN" - maybe "account manager" or something similarly highfalutin'?

[cielf]

Membership manager? Judi bumped the current question to Lynn, and there has been no response as yet. I expect this will be quiescent until I get back.

[cielf]

I've touched base with Judi/Lynn and uploading a csv would definitely be ok. I've asked Lynn to export her current spreadsheet to csv to serve as the basis.

[cielf]

@dorner, @sunsheeppoplar The addition of another role for the upload might take this a little beyond the original ask -- do we need to split this into [adding the role] and [allowing people with the role to upload a csv , and interpret that csv]?

[dorner]

I mean... we could, but I'm not sure it would make sense to add a role that can't actually do anything.

[cielf]

The thing the role could do would be to upload the NDBN members file. That's it for now. Do we have a reasonable alternate way to handle that?

[cielf]

But I could see allowing them to see the NDBN annual reports in the future. For NDBN members only, of course.

[cielf]

This wouldn't really be in the same slice as org_admin and org_user. It's a role that allows the NDBN to upload the csv file rather than us having to scrape it from their webseite.

[dorner]

Sorry - I meant that having a ticket to add the role, where that role can't do anything until the next ticket that adds that functionality, would leave the repo in a confusing state IMO. So I think leaving the requirements together does make sense.

[cielf]

(Nods) Let's make it so, then? @sunsheeppoplar -- I don't know if this is still in your comfort zone -- please comment one way or another. We've changed it / left you hanging long enough that if someone else jumps onto it, we'll likely assign it to them, though.

[sunsheeppoplar]

Happy to have it unassigned from me as I have another ticket in flight with the repo that I've been meaning to get to. If I finish that and no one has picked this up, I can come back to it. Glad to have gotten the conversation rolling either way! @cielf

[elasticspoon]

@cielf was pairing with @veezus on this ticket and a could of questions came up:

1. Where should the file upload behavior live? we were thinking it could be a side bar link

to a page that has the members and a file upload for the csv:

We were thinking of creating the NDBN role to allow this.

2. How should the role be given? Just allow a super user to add it to another user when needed?

3. Should superuser roles also be able to do this behavior or should we by default give them the ndbn role? Or should they have to give themselves the role to be able to upload?

[cielf]

1. I could see it going under the Community umbrella. The first level of the left hand menu is pretty long, so I'm loathe to add new things to it. We might also want to consider that the NDBN role won't have any of the other things in the left hand menu, and won't have a dashboard. So we'll need to consider /test what they are seeing. There are two people I know of who would potentially have this role. One is the director of a bank, and has orgadmin access on one organization, and the other is only NDBN.
2. Yes, I think it's stable enough that we can just leave it with the superuser, rather than putting in the infrastructure to have someone with NDBN access be able to grant someone else NDBN access.
3. I don't see any need for the superusers to be able to do this directly. They won't normally have access to the file that would have to be uploaded in any case.