Google Script UrlFetchApp.fetch returns a 404 eventhough the page exist

1
votes

I am sending automated report with Google spreadsheet and Google script.

So far it was working perfectly. But somehow when I try to create a new report to be emailed, the function "UrlFetchApp.fetch" return a 404. The same situation happened when I tried copying the old report.

The line with "UrlFetchApp.fetch" give me this error:

Request failed for https://docs.google.com/spreadsheets/d/1qm_bCKn4MbLKy7AIuIeu7bTZcTk8ObYBln0GAxwfsX8/pub?gid=195635557&single=true&output=pdf returned code 404. Truncated server response

It seems that I am not the only one having the issue but I cannot find any solution to it.

Here is the code:

function emailSpreadsheetAsCSV() {
  var ss = SpreadsheetApp.openById("1qm_bCKn4MbLKy7AIuIeu7bTZcTk8ObYBln0GAxwfsX8");
  var url = ss.getUrl();
  url = url.replace(/edit$/,'');     
  var token = ScriptApp.getOAuthToken();
  var sheets = ss.getSheets(); 
  //make an empty array to hold your fetched blobs  
  var blobs = [];
  for (var i=0; i<sheets.length; i++) {
      var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/1qm_bCKn4MbLKy7AIuIeu7bTZcTk8ObYBln0GAxwfsX8/pub?gid=195635557&single=true&output=pdf", {
        headers: {
          'Authorization': 'Bearer ' +  token
        },
        'muteHttpExceptions': false
      });
    //convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.csv');
  }
  //create new blob that is a zip file containing our blob array
  var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip');     
return blobs[0];       
}

Thanks a lot for your help.

Aymeric.

4
google-apps-scriptgoogle-docs
Write a good question: stackoverflow.com/help/how-to-ask Incude code: meta.stackoverflow.com/a/302091/2213940. – Zig Mandel
Sorry. Hope it is clearer now. – Aymeric Flaisler
specify which of the multiple calls fail. post only needed code. – Zig Mandel
How about now? Is it clearer? – Aymeric Flaisler
look in the oficial docs how to properly convert a doc to a pdf. its a method. – Zig Mandel

4 Answers

2
votes

I've gotten this problem too and after some research, Spence Easton (question 34573295) solved my issue. I simply added this bogus call to drive so access is granted, and so the script can now get to your file. Add this near the top before trying to grab the url:

var bogus = DriveApp.getRootFolder();

Now it runs fine with no 404 errors.

0
votes

Remove the headers from the request:

var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/1qm_bCKn4MbLKy7AIuIeu7bTZcTk8ObYBln0GAxwfsX8/pub?gid=195635557&single=true&output=pdf", {

        'muteHttpExceptions': false
      });

Check if it works for you.

0
votes

You use the "pub" url so are you sure the spreadsheet is published on the web ? If you get a 404 it means the page is not published. If file is published the bearer is not necessary as it is public now.

After I don't really understand your code because you iterate all the sheets of your file

for (var i=0; i<sheets.length; i++)

but the param "gid" (pub?gid=195635557&single=true&output=pdf) for the url in your urlfetch is fixed ?

Second point you get the file as pdf and after you create a csv ?

Why not get the file as csv directly : https://docs.google.com/spreadsheets/export?id=TheIdOfTheFile&exportFormat=csv

By adapting this code you can get the csv directly, see : https://stackoverflow.com/a/28503601/3556215

Take care you will get the first page and not others.

Stéphane

0
votes

Try with:

var response = UrlFetchApp.fetch(url, options);
var result = JSON.parse(res.getContentText());