CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
wonk
wonk

Reputation: 92

Request failed for https://docs.google.com returned code 401 - saving and emailing PDF files from Google Sheets

I have basically non-existent experience in Javascript, but know a little bit of Python so I figured I was up to the task of Frankensteining a couple of pre-made scripts together which I found online. The idea is to look through a list of data, then send PDFs of the appropriate spreadsheet to the desired e-mail address. I have copied my attempt below.

// This constant is written in for rows for which an email has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 16; // First row of data to process
  var numRows = 1; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 6); // Fetch the range of cells
  var data = dataRange.getValues(); // Fetch values for each row in the Range.

  const token = ScriptApp.getOAuthToken();
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the currently active spreadsheet URL (link)
  const subject = 'Monthly Invoice'; // Subject of email message
  const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId()); // Base URL 
  const exportOptions = // Specify PDF export parameters From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
    'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=A4' + // paper size legal / letter / A4
    '&portrait=true' + // orientation, false for landscape
    '&fitw=true&source=labnol' + // fit to page width, false for actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid='; // the sheet's Id
  const sheets = ss.getSheets();

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[4];
    var message = row[3];
    var emailSent = row[5];

    var client_id = row[0];
    var client_sheet = ss.getSheetByName(client_id);

    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
      const blobs = []; // make an empty array to hold your fetched blobs

      // Convert individual worksheets to PDF
      const response = UrlFetchApp.fetch(url + exportOptions + client_sheet, {
        headers: {
          Authorization: 'Bearer ${token}'
        }
      });

      // convert the response to a blob and store in our array
      blobs[i] = response.getBlob().setName('${client_sheet}.pdf');

    // If allowed to send emails, send the email with the PDF attachment - 500 emails per day standard
    if (MailApp.getRemainingDailyQuota() > 0)
      GmailApp.sendEmail(emailAddress, subject, message, {
        attachments: [blobs[i]]
      });
    sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
    // Make sure the cell is updated right away in case the script is interrupted
    SpreadsheetApp.flush();
  }
}
    // create new blob that is a zip file containing our blob array
    // const zipBlob = Utilities.zip(blobs).setName(`${ss.getName()}.zip`);
    // optional: save the file to the root folder of Google Drive
    // DriveApp.createFile(zipBlob);
}

I'm currently running into this error, however - and honestly I'm lost.

Request failed for https://docs.google.com returned code 401

Request failed for https://docs.google.com returned code 401. Truncated server response: <HTML> <HEAD> <TITLE>Unauthorized</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000"> <H1>Unauthorized</H1> <H2>Error 401</H2> </BODY> </HTML> (use muteHttpExceptions option to examine full response) (line 39, file "send_emails")

If it helps, line 39 is: const response = UrlFetchApp.fetch(url + exportOptions + client_sheet, {

Could somebody please assist? Thank you.

Upvotes: 2

Views: 5994

Answers (1)

Tanaike
Tanaike

Reputation: 201388

If you are using the script in your question, how about this answer? Please think of this as just one of several answers.

Modification point:

  • Unfortunately, in the current stage, the template literal, which was added at ES2015, cannot be used with Google Apps Script. I thought that the reason of your issue might be this.

Modified script:

Please modify your script as follows.

From:

Authorization: 'Bearer ${token}'

To:

Authorization: 'Bearer ' + token

And

From:

blobs[i] = response.getBlob().setName('${client_sheet}.pdf');

To:

blobs[i] = response.getBlob().setName(client_sheet + '.pdf');

References:

If I misunderstood your question and this was not the result you want, I apologize.

Added:

I noticed one more modification point. Please modify your script as follows.

From:

var client_sheet = ss.getSheetByName(client_id);

To:

var client_sheet = ss.getSheetByName(client_id).getSheetId();
  • In order to retrieve gid, please use getSheetId().

Updated: December 19, 2020:

Now, Google Apps Script can use V8 runtime. Ref So the template literals can be used. But there is an important point. In this case, please use the backtick (grave accent) as follows.

Authorization: `Bearer ${token}`

and

blobs[i] = response.getBlob().setName(`${client_sheet}.pdf`);

Upvotes: 4

Related Questions