我有一个谷歌表单,可以捕获电子表格中的响应。目前,每次做出新响应时,它都会创建一个新工作表。
我现在尝试将“邮寄活动工作表脚本”添加到创建新工作表的现有脚本中。
但是我收到错误“请求失败https://docs.google.com/spreadsheets/d/SS_ID/export https://docs.google.com/spreadsheets/d/SS_ID/export?”围绕以下内容
var result = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
我已将这篇文章用作guide https://productforums.google.com/forum/#!topic/docs/kh5mJJdb_x8对于发送电子邮件部分。
这是我正在使用的整个脚本。
function onSubmit(e){
Logger.log('submit ran');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//Get last row of data
var lastRow = sheet.getLastRow();
var colB_Data = sheet.getRange(lastRow, 2).getValue();
//create sheet name with order no.
ss.insertSheet(colB_Data);
//order formula
var sheets = ss.getSheets()[1];
var cell = sheets.getRange("b2");
cell.setFormula("=TRANSPOSE('Form Responses 1'!B1:AR1)");
var cell = sheets.getRange("c2");
cell.setFormula("=sheetName()");
var cell = sheets.getRange("c3");
cell.setFormula("=transpose(FILTER('Form Responses 1'!C:AR,'Form Responses 1'!B:B=C2))");
var cell = sheets.getRange("d5:d44");
cell.setFormula("=C5*vlookup(B5,'RG Cost'!B:E,4,0)");
var cell = sheets.getRange("d5:d44");
cell.setNumberFormat("[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0")
var cell = sheets.getRange("c47");
cell.setFormula("=sum(C5:C44)");
var cell = sheets.getRange("d47");
cell.setFormula("=sum(D5:D44)");
var cell = sheets.getRange("b47");
cell.setValue('TOTAL');
//Send active sheet as email attachment
var ssID = SpreadsheetApp.getActiveSpreadsheet();
var email = Session.getUser().getEmail();
var subject = "Order no.";
var body = "Hello";
var token = ScriptApp.getOAuthToken();
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?" + "format=xlsx" + "&gid=" + "&portrait=true" + "&exportFormat=xlsx";
var result = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var contents = result.getContent();
MailApp.sendEmail("[email protected] /cdn-cgi/l/email-protection",subject ,body, {attachments:[{fileName:colB_Data+".pdf", content:contents, mimeType:"application//pdf"}]});
};