UrlFetchApp from drive.google.com STOPPED WORKING - error 403

[Moosy Research]

It looks like Google made some changes today (February 5, 2020) that impacted several of my scripts. The UrlFetch directly to drive.google.com stopped working (error 403 / forbidden) and can not access a file that is flagged public.
UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=<<file id>>")

A.) working: This file link from a browser works when you click the link
https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ

B.) failed: The URLFetch to the same url failed after February 5
var DataSet = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getContentText(); 

Error: We're sorry...... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now. See https://support.google.com/websearch/answer/86640

Redirect
I noticed that The above url is redirected to the url below. When I use the url below it does work in the script.
https://doc-0g-9k-docs.googleusercontent.com/docs/securesc/ha0ro937gcuc7l7deffksulhg5h7mbp1/9hd91fs45kj9n2qh7nsvhpacnt7pok06/1580978700000/15098391250088696476/*/1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ?e=download


Q:  So the conclusion is that the redirect stopped working and throws in an error message.  Additional Security measures are great but it's bummer when a vital part of a script breaks.

What would you recommend when multiple users (scripts) need anonymous access to a file in drive?

wkr,

Moosy

[Andrew Roberts]

Google have just enabled the V8 JS engine on Apps Script and that seems to be throwing up various issues.

[Alan Wells]

You could programmatically grant sharing permissions to specific users instead of making the file public to everyone.  But, whether that would fix the error, I have no idea.

The DriveApp service does have a getDownloadUrl() method on the file class.

https://developers.google.com/apps-script/reference/drive/file.html#getdownloadurl

Are you building the download url with a string formula?

[Moosy Research]

I don't care about permissions and just want the file to be public to the world without any authentication or authorization.

This was working with UrlFetchApp for several years and broke with V8 and the 'new security policy'.

Workaround (DriveApp): 

I 'solved' it for now by using the DriveApp service but this work around requires the script to have full permissions (read/write access) to Google Drive Permissions and this is something my users really don't want !!!! 

var DataSet = DriveApp.getFileById("1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getBlob().getDataAsString();    

Original line (UrlFetchApp):

var DataSet = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getContentText(); 

Any other suggestion how to avoid using the DriveApp dependency just to fetch a public file from Drive is very welcome !!!

Thanks,

M

[Riël Notermans]

There IS a limit on exporting files trough export= , could it be the case you hit it?

Op do 6 feb. 2020 21:31 schreef Moosy Research <moos...@gmail.com>:

I don't care about permissions and just want the file to be public to the world without any authentication or authorization.

This was working with UrlFetchApp for several years and broke with V8 and the 'new security policy'.

Work around (DriveApp): 

I 'solved' it for now by using the DriveApp service but this is an authorization my users really don't like !!!!

var DataSet = DriveApp.getFileById("1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getBlob().getDataAsString();    

Original line (UrlFetchApp):

var DataSet = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getContentText(); 

Any other suggestion how to avoid using DriveApp just to fetch a public file from Drive is very welcome !!!

Thanks,

M

On Thursday, February 6, 2020 at 2:00:36 PM UTC+1, aj.addons wrote:

You could programmatically grant sharing permissions to specific users instead of making the file public to everyone.  But, whether that would fix the error, I have no idea.

The DriveApp service does have a getDownloadUrl() method on the file class.

https://developers.google.com/apps-script/reference/drive/file.html#getdownloadurl

Are you building the download url with a string formula?

On Thursday, February 6, 2020 at 12:37:45 AM UTC-5, Moosy Research wrote:

It looks like Google made some changes today (February 5, 2020) that impacted several of my scripts. The UrlFetch directly to drive.google.com stopped working (error 403 / forbidden) and can not access a file that is flagged public.
UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=<<file id>>")

A.) working: This file link from a browser works when you click the link
https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ

B.) failed: The URLFetch to the same url failed after February 5
var DataSet = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getContentText(); 

Error: We're sorry...... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now. See https://support.google.com/websearch/answer/86640

Redirect
I noticed that The above url is redirected to the url below. When I use the url below it does work in the script.
https://doc-0g-9k-docs.googleusercontent.com/docs/securesc/ha0ro937gcuc7l7deffksulhg5h7mbp1/9hd91fs45kj9n2qh7nsvhpacnt7pok06/1580978700000/15098391250088696476/*/1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ?e=download


Q:  So the conclusion is that the redirect stopped working and throws in an error message.  Additional Security measures are great but it's bummer when a vital part of a script breaks.

What would you recommend when multiple users (scripts) need anonymous access to a file in drive?

wkr,

Moosy

--
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/bcdc4e64-a3c8-452d-88e9-e85a27fa624e%40googlegroups.com.

[Moosy Research]

On Friday, February 7, 2020 at 7:14:41 AM UTC+1, Riël Notermans wrote:

Q: There IS a limit on exporting files trough export= , could it be the case you hit it?

No, I don't think so.

1. You can reproduce this by running this script on any Google account
   function myFunction()  {  var DataSet = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ").getContentText(); }
2. This code was running for > 2 years 

I think this is related to the Google's Risk Engine and IP addresses.

With Google Script we share a large pool of IP addresses.

When a workload gets executed it's 'randomly' assigned to a node and gets an IP address.

I assume that the risk engine detects too much URLFeteches from the Google IP addresses and blocks it with an error 403

Error: We're sorry...... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now. See https://support.google.com/websearch/answer/86640

If this would happen from a normal client machine, it would ask for a reCAPTCHA.

It looks like the security layer does not trust the Google App Script IP addresses and it's questionable if this is a bug or working as designed. However, the conclusion is that an anonymous/public fetch with UrlFetchApp to something on Drive is not allowed.

wkr,

M

[Moosy Research]

In addition to my previous comment:
Below are a few lines of code that output the IP address of the node and the UrlFetchApp response.

function Demo(){
 
  var ipa = JSON.parse(UrlFetchApp.fetch("http://ip-api.com/json?fields=query").getContentText()),
      uri = 'https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ',
      opt = {'muteHttpExceptions' : true};
 
  Logger.log('IP Address = ' + ipa.query);
  Logger.log('HTTP Response code = ' + UrlFetchApp.fetch(uri,opt).getResponseCode() );
  Logger.log('HTTP Output = ' + UrlFetchApp.fetch(uri,opt).getContentText() );
}

pop-in the following url in your browser so you can clearly see the file is publicly accessible

https://drive.google.com/uc?export=download&id=1qNBgV2t-D5n_ut-4QINMhKg6ZJXt2-VZ

I strongly believe the Google Risk Engine blocks the request (403 Forbidden) based on context of the request. (IP Address) 

I extended the above script so the output is written to a sheet and let it run every minute using a Trigger:

It clearly shows the script is running from different nodes (IP addresses) and that it had ONE SUCCESSFUL fetch  !!

107.178.194.170	403	2/7/2020 11:43:38
107.178.192.108	403	2/7/2020 11:42:40
107.178.192.106	403	2/7/2020 11:41:37
107.178.192.104	403	2/7/2020 11:40:39
107.178.193.54	403	2/7/2020 11:39:38
107.178.192.108	200	2/7/2020 11:38:31
107.178.193.52	403	2/7/2020 11:37:41
107.178.194.172	403	2/7/2020 11:36:40
107.178.195.92	403	2/7/2020 11:35:40
107.178.194.170	403	2/7/2020 11:34:38
107.178.192.108	403	2/7/2020 11:33:36
107.178.194.170	403	2/7/2020 11:32:41

wkr,

M

[Moosy Research]

Wrap-up of this threat 

To read a file from Drive using GAS, you can use:

UrlFetchApp - https://drive.google.com/uc?export=download&id=<<fileID>>

Starting February 5, 2020, UrlFetchApp to get a file from Google Drive does not work anymore and is blocked with the following message:

Error 403 / Forbidden:

Error: We're sorry...... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now. See https://support.google.com/websearch/answer/86640

Alternative:

There is an alternative way to read a file using the Advanced Drive Service (DriveApp.getFileById) but this requires authorization that would be nice to avoid. 

Required Authorization: 

Google Drive - See, edit, create and delete all of your Google Drive files

[Andrew Roberts]

In case it is useful, I came across this from Laura Taylor the other day: https://github.com/techstreams/TSDynamicUrls - "Google Apps Scripts for leveraging the URL power of Google document! ".

--
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/19d188a2-06ac-4d90-9ef7-e6e050755d44%40googlegroups.com.

[Moosy Research]

see official response from the GAS team: https://issuetracker.google.com/issues/149154237

[Alan Wells]

Interesting situation.  The reason given by the Google person in the Issue Tracker for why the download URL doesn't work with UrlFetchApp.fetch() is that the download URL is only meant to be used in the browser's address bar, and not from a server request.  And it's interesting that they mention reCAPTCHA as being used to block the UrlFetchApp.fetch() request.

Google is constantly trying to stay ahead of the people trying to break reCAPTCHA.  Listen to the NPR interview.

https://www.npr.org/2019/05/03/720097308/google-replaces-captcha-with-recaptcha-a-more-effective-way-to-decide-who-is-hum

As far as continuing to use a UrlFetchApp.fetch() request to get the file content, here is what I'd do.

Create a Web App in your account.

Your Web App will be the target of the UrlFetchApp.fetch() request, not the download URL.

Your Web App will get the file content, and because it's your Web App and your file, there won't be a problem.

Your Web App will return the file content to the UrlFetchApp.fetch() request instead of downloading the file content directly.

So, your Web App will be an intermediary in the process.  You'll need to use Content Service to return the file contents. 

see:

https://developers.google.com/apps-script/guides/content

At the documentation I don't see any restrictions on size.

Any and all users can have access to your published Web App if you publish it to give anonymous access to anyone.  None of your users would need to create the Web App or have a copy of it running in their Google account.  The Web App would access your file in your account.  Typically a Web App is for displaying a Web Page, but it can also be used to return content.  When you publish a Web App, you get a URL that is used to access the Web App.  There are two types of Web App urls.  "dev" the development version, and "exec" - the public / production version.  The "dev" version is always the current code, but the exec version won't run the latest code unless you explicitly create and publish a new version.

A Web App can have either or both of the two reserved function names named doGet() or doPost().  Those reserved function name functions are event handlers for an HTTPS GET and POST request made to the published url of the web app.

If you make a POST request to the Web App published url, then the doPost() function will run.  That Web App is in your account running under your authority, and if you publish it to be accessible to anyone, then anyone can run it.  Most people are not going to know the file ID in the URL, so not just anyone is going to run it.

But, there are things you can do to increase security if you need to.  You can publish the Web App as an execution API, or send in a password in the POST payload.

[Faustino Rodriguez]

In similar situations (not sure in your case) what we have done is like ...

- using Drive.File to get the content of a Drive file

- but using the scope /drive.file

- that'd require using the Drive File Picker for the user to select the intended file to authorize the access

[Moosy Research]

This Web App Middle Tier solution (as suggested by aj.addons) works like a charm and does not require the DriveApp API at the end point at all !!!

Very creative !! Thanks a ton.

Moosy

[Kim Nilsson]

That sounds great, Moosy, but I don't understand how to use it at all.

Has someone documented in more detail how to use the suggestion?

With example code, so noobs like me can replace a current UrlFetchApp setup.

Over in my thread one of my guesses is that UrlFetchApp is the culprit, but only after extensive use, as the exact same code works fine in another G Suite organisation (not just another user account... a completely other G Suite organisation/domain).

[Alan Wells]

I've updated my answer at:

https://groups.google.com/d/msg/google-apps-script-community/xw5hqF-Ommk/nH9MlGIkGAAJ

to include some more information.

[Moosy Research]

Hi Kim,

Make sure you read the info provided by aj.addons

Traditionally UrlFetchApp is directly going to source file (system). 

With this 'trick' you go to a middle tier (proxy) service that will fetch the file for you and pass it through.

Component A:  a special script that is running/published as a webapp service. When it's published as a webapp, you will get a unique url to run this service.

sample webapp:

function doGet() {

  var result = DriveApp.getFileById("your file ID").getBlob().getDataAsString();     

  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

}

Component B: your normal script will now fetch from the webapp service, so this will use the url of the webapp service.

sample from your script:

function FetchFile() {

  var response = UrlFetchApp.fetch("webapp service url (Component A url)");  

  Logger.log( response.getContentText() );

}

This is an example and needs to be adjusted to your use case.

Hope this helps,

M

[Kim Nilsson]

Thank you, Moosy.

Definitely worth a try, as my current web app is dead in the water in one of my domains.

It can't access the sheet anymore. It could, and did, several hundred times over the last two weeks, and now it can't anymore.

[Kim Nilsson]

@Moosy,

I solved my own 401 problem.

Sharing the source file anyone with the link was the winner!

However, my web app is still published as only accessible by users in my domain.

So, I haven't tried to set up a dual-web app solution as was suggested here.

I will try that, as part of my learning more JS, but I currently have a working web app. :-)

[Ami A]

That would be AWESOME if someone would provide better documentation then what is current.

On Wednesday, February 19, 2020 at 7:57:26 AM UTC-5, Kim Nilsson wrote: