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 = '?'.replace('SS_ID', ss.getId()); // Base URL
const exportOptions = // Specify PDF export parameters From:
'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 returned code 401
Request failed for 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.
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./spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId()); // Base URL
const exportOptions = // Specify PDF export parameters From: https://code.google./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. returned code 401
Request failed for https://docs.google. 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.
Share Improve this question edited Oct 22, 2019 at 12:38 wonk asked Oct 22, 2019 at 11:28 wonkwonk 921 silver badge10 bronze badges 5- its saying Unauthorized. please check your api credits or try to get full resposnse following error – Ravi Commented Oct 22, 2019 at 11:34
- @Ravi That's weird, my call for ScriptApp.getOAuthToken() doesn't error at all, so it's definitely getting something. How does muteHttpExceptions work? I can't seem to work that out either – wonk Commented Oct 22, 2019 at 11:55
- are you getting token? – Ravi Commented Oct 22, 2019 at 12:00
- @Ravi I define the token on line 11 and then call it for "Authorization" on 41 – wonk Commented Oct 22, 2019 at 12:02
- not sure. but dont you would need to pass options as object in fetch method paramater?? – Ravi Commented Oct 22, 2019 at 12:08
1 Answer
Reset to default 4If 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:
- Basic JavaScript features
- Template literals
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 usegetSheetId().
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`);
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745666021a4639127.html
评论列表（0条）