search

Synthoid / ExportSheetData

Add-on for Google Sheets that allows sheets to be exported as JSON or XML.
MIT License
234 stars 46 forks source link

App is blocked because it tried to gain sensitive information #163

Open akerson opened 4 months ago

akerson commented 4 months ago

Been using this extension for years, it now doesn't work.

image-8

Google suggests the security feature improvements of Google has broken it. Any chance we could get a fix??

vovabob commented 2 months ago

Yes. Same thing.

Synthoid commented 2 months ago

Sorry, missed the initial comment here. I'll take a look and see what I can do!

Synthoid commented 2 months ago

Hmm, I am able to use the add on with no issue. Can you check if multiple google accounts are signed in? If so, try signing out of all but one and running ESD again. You should be able to sign back into the other accounts after that. If things don't work after that, let me know some more details like:

  1. Are multiple accounts signed in?
  2. Are you using a personal or workplace account?
  3. What browser is being used? (guessing chrome from the screenshot, but good to confirm)
  4. What country are you accessing the account from?
akerson commented 2 months ago

Hi!

  1. Yes there's three of us, we use it to development the json exports for our video game. One person is able to use it no problem so this tracks with what you are suggesting.
  2. Personal Gmail accounts
  3. Chrome in all three cases
  4. USA for all three

We'll try having user 1 sign out and attempt again. It's a bit of a pain as sheets is tied to logging into everything with your Google account but it's probably more manageable than not having access to this tool as it's very unique.

akerson commented 2 months ago

Hello,

I am now owner of this sheet. I cannot install the extension. I'm assuming if you have it installed already there is no issues. Same error as before.

Maybe something like this would help? https://developers.google.com/apps-script/guides/services/authorization#manual_authorization_scopes_forand

Synthoid commented 2 months ago

Hmm, do you already have ESD installed for your account? Or are you attempting to install it now?

If you already have it installed, are you able to make a new sheet and export JSON/XML from that?

Synthoid commented 2 months ago

Actually, I just tried doing so with my work account and I saw the blocked warning too...

I may need to update the Google Cloud Project for ESD to ensure its scopes are still correct. It could be that Google doesn't like granting access to Drive for file management purposes anymore, or at least has stricter scopes for it.

Synthoid commented 2 months ago

I think I have to do some OAuth verification steps on the project to fix this issue. That will require some web work on my end. This will take me a couple days due to my upcoming schedule unfortunately.

I will try to have the issue resolved ASAP. Sorry for the inconvenience!

akerson commented 2 months ago

Hi thanks! Completely understandable about other priorities - I'll wait patiently!

Synthoid commented 1 month ago

Alright, I have ESD resubmitted for verification. Had to do a surprising amount of web dev and video production to verify an open source add-on, but hopefully it will be back up soon.

Synthoid commented 1 month ago

Quick update. I've been back and forth with Google's verification team this past week. I believe I've ironed out the last of their concerns with the latest round of revisions so hopefully the next time I hear from them it will be them approving everything.

akerson commented 1 month ago

Awesome keep us posted! Appreciate helping all of us out we love this addon 🥰

khambadkone commented 1 month ago

@Synthoid thanks for following up on this. We are still facing this issue ; do you mean that your fix in https://github.com/Synthoid/ExportSheetData/issues/163#issuecomment-2227441128 is yet to be reviewed by Google, and once it is, we wont have this issue ?

Other than signing out of all other accounts, what is the known workaround until then?

Synthoid commented 1 month ago

To clarify, I have made requested revisions to various ESD resources and I am currently waiting to hear back from Google.

The turnaround on that is usually 24-72 hours so I should hopefully hear back within the next day.

Until I resolve the verification issues, there doesn't really seem to be a great workaround unfortunately. From my understanding, only around 100 people could actively choose to bypass the warning and that was probably a first come, first served situation.

If you can wait, I believe this will be resolved this week, ideally after this latest round of revisions. Barring some needling from Google's verification team...

I'm sorry I've let ESD languish and get into this state. I should have cleaned house months ago to prevent a lapse in service.

BizarreFog commented 1 month ago

To clarify, I have made requested revisions to various ESD resources and I am currently waiting to hear back from Google.

The turnaround on that is usually 24-72 hours so I should hopefully hear back within the next day.

Until I resolve the verification issues, there doesn't really seem to be a great workaround unfortunately. From my understanding, only around 100 people could actively choose to bypass the warning and that was probably a first come, first served situation.

If you can wait, I believe this will be resolved this week, ideally after this latest round of revisions. Barring some needling from Google's verification team...

I'm sorry I've let ESD languish and get into this state. I should have cleaned house months ago to prevent a lapse in service.

Thanks for working through this to get it back up! Love your tool and am excited to pick it back up once Google approves it

Synthoid commented 1 month ago

So... some good news, and some bad news. The good news is that ESD has cleared Google's verification process!

The bad news is there is apparently another step. ESD has to clear a CASA assessment by a trusted third party. My initial look into Google's provided resources is not looking great. To even get a CASA assessment it's looking like I'd need to shell out $500+. That would become an annual expense as the evaluation has to happen every year... Even worse, the best evaluation process that gives maximum confidence for the application's security is a whopping $3000+ annually.

I'm going to look into my options here to see if I can get an exemption since it's just an Apps Script add-on that isn't storing user data and there were such exceptions in the past. This certainly wasn't part of the verification before but if it is indeed required now... I'm not sure that is an expense I can handle. The $3000+ evaluation absolutely isn't.

For the time being, that does mean ESD will be down for a little while longer. I'm sorry for the delay.

Synthoid commented 1 month ago

After looking into it some more, I may be able to avoid the whole CASA process as that is only required because ESD requests root Drive authorization for some processes.

Specifically the "Replace existing file(s)" option is the main thing using that scope to streamline workflows. I think I'll have to rework that so users have to select a file to replace instead of automatically clearing files in the export folder. It's a worse workflow, but that should mean ESD will not cost me $3000+ a year...

Synthoid commented 4 weeks ago

vb-brock-coffee

Quick update. I've been banging my head against my keyboard this weekend trying to get things working. Unfortunately the scope issue goes deeper than I thought and all of the existing DriveApp code ESD uses to create, not just update/replace, JSON/XML files require the restricted auth/drive scope. This is not viable as it means I'll have to go through the CASA evaluation every year.

The most promising approach seems to be migrating over to the advanced Drive service so I can use the reduced auth/drive.file scope. I've made some minor progress on that, but it's looking like I'll be enduring some trial and error to get this fully working.

sebinfinity commented 2 weeks ago

@Synthoid Thank you for looking into this issue. The issue is also happening on my end.

Looking forward to being able to use your amazing plugin again!

Synthoid commented 2 weeks ago

Alright, time for another quick good news/bad news update.

The good news, I have file exports working again! The migration over to the advanced Drive service to create files was painful, but with enough persistence I've bashed that brick wall down.

The bad news, ESD in general is going to be less user friendly going forward and I'm probably going to have to rip out file replacement and export folder customization. The more restricted auth/drive.file scope pretty much only lets me interact with files that you personally have created with ESD, which creates a minefield of potential errors when calling Google's services. I have to counteract this with lots of try-catch statements (made even better because now the export method has to be async). I believe there is some potential way to get around this using the google file picker but, like the Drive API, documentation is somewhat lacking so I have to go forum spelunking. Posts like this make me a little worried, but I'm optimistic I can get things working.

My plan for now is to remove the Replace existing file(s) and Export Folder settings to get an updated build out the door so people can actually use ESD again. This does mean that exported files will be dumped into your root My Drive folder for the time being, but it's better than nothing. Fortunately I was able to keep the post-export modal popup functionality, so you will still see links to the exported file and a download button to get it. Just keep an eye on your root folder so you don't drown in .json and .xml files...

I'm going to take a couple more days to try and get the picker working, but I'm hoping ESD will be back up within a week after making all these changes.