Hi everyone and thanks a lot for reading this.
I have some code that was based on SendPDF of Jiayao. Original here: SendPDF of Jiayao
I have a Google Spreadsheet that generates a PDF, send email, and save the pdf in Google Drive ( I using Google Apps).
It always worked. However, when I moved to the New Google Drive no longer works.
When I run I get the error:
Request failed http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=1KWJC2tHk6uxrrIYwaf3GOe5NKsPzQRaYQy2pPmwfx_M&exportFormat=pdf&gid=954198957 retornou o código 401. Resposta truncada do servidor:< HTML > < HEAD > < TITLE >Unauthorized < BODY BGCOLOR="#FFFFFF" TEXT="#000000"> < H1 >Unauthorized Error 401 (use a opção muteHttpExceptions para examinar a resposta completa)
Below, my code:
// Copyright 2010 Jiayao Yu
//
// ORIGINAL See usage and updated version on http://gist.github.com/405466
var TOKEN_CELL = 1;
var EMAIL_CELL = 2;
var BCC_CELL = 3;
var SUBJECT_CELL = 4;
var BODY_CELL = 5;
var SHEET_NAME_CELL = 6;
var SHEET_RELATORIO = 7;
var SPREADSHEET_URL = "http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=";
var DATA_RELATORIO = 8;
var BUSCA_GID = 9;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
menuEntries = [ {name: "Enviar relatório", functionName: "sendAsPdf"}];
ss.addMenu("Relatório HRT", menuEntries);
var configSheet = getConfigSheet();
configSheet.getRange(1, TOKEN_CELL).setValue("Auth Token");
configSheet.getRange(1, EMAIL_CELL).setValue("Email");
configSheet.getRange(1, BCC_CELL).setValue("Bcc");
configSheet.getRange(1, SUBJECT_CELL).setValue("Subject");
configSheet.getRange(1, BODY_CELL).setValue("htmlBody");
configSheet.getRange(1, SHEET_NAME_CELL).setValue("Export sheet name");
configSheet.getRange(1, SHEET_RELATORIO).setValue("Relatorio");
configSheet.getRange(1, DATA_RELATORIO).setValue("DatadoRelatorio");
configSheet.getRange(1, BUSCA_GID).setValue("BGID");
}
function sendAsPdf() {
// Pergunta se deseja enviar email
var PerguntaEmail = Browser.msgBox('Enviar Email', 'Deseja enviar email para ' + getConfig(EMAIL_CELL) , Browser.Buttons.YES_NO);
if (PerguntaEmail == 'yes' ) {
var configSheet = getConfigSheet();
var id = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheetName = getConfig(SHEET_NAME_CELL);
var arquivoName = getConfig(SHEET_NAME_CELL) + getConfig(SHEET_RELATORIO);
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!dataSheet) {
Browser.msgBox("Can't find sheet named:" + sheetName);
return;
}
// Atualiza a data do Relatório na aba Relatorio_Pneumologia
// Browser.msgBox(getConfig(DATA_RELATORIO));
// var formattedDate = Utilities.formatDate(new Date(), "GMT-3", "dd-MM-yyyy");
dataSheet.getRange(3,2).setValue(getConfig(DATA_RELATORIO));
// Logger.log(formattedDate);
// Browser.msgBox(formattedDate);
// Envia EMAIL
var dataSheetIndex = dataSheet.getIndex() - 1;
// Original: var url = SPREADSHEET_URL + id + "&exportFormat=pdf&gid=" + dataSheetIndex;
var url = SPREADSHEET_URL + id + "&exportFormat=pdf&gid=" + getConfig(BUSCA_GID);
var auth = "AuthSub token=\"" + getConfig(TOKEN_CELL) + "\"";
var res = UrlFetchApp.fetch(url, {headers: {Authorization: auth}});
var content = res.getContent();
var responseCode = res.getResponseCode();
if (responseCode != 200 || res.getContentText().indexOf("/ServiceLoginAuth") != -1) {
Logger.log("Fetch url:" + url + " failed with " + responseCode);
Browser.msgBox("Error occurred when exporting spreadsheet to pdf, it might be caused by auth token being expired");
return;
}
var bcc = getConfig(BCC_CELL);
Logger.log("BCC to:" + bcc);
var attachments = [{fileName:arquivoName + ".pdf", content: content, mimeType:"application/pdf"}];
MailApp.sendEmail(getConfig(EMAIL_CELL), getConfig(SUBJECT_CELL),
"", {attachments:attachments, bcc: bcc, htmlBody: getConfig(BODY_CELL)});
Browser.msgBox("Email enviado com sucesso para: " + getConfig(EMAIL_CELL));
// Salvar versao do relatório no Google Drive
var Impressao = Browser.msgBox('Salvar Relatorio', 'Deseja salvar o relatório no Google Drive?', Browser.Buttons.YES_NO);
if (Impressao == 'yes' ) {
// Codigo inspirado em http://stackoverflow.com/questions/12881547/exporting-spreadsheet-to-pdf-then-saving-the-file-in-google-drive
// Salva no Google Drive
var NomedoArquivo = arquivoName + ".pdf"
DocsList.createFile(res).rename(NomedoArquivo);;
Browser.msgBox('Arquivo gravado em seu Google Drive. Pode fechar da planilha.');
}
else { Browser.msgBox('Operação concluída com sucesso. Pode fechar da planilha.')
}
}
// FIM DO THEN do IF se deseja enviar email
else { Browser.msgBox('Ok. Pode fechar da planilha.')
}
// FIM DO IF se deseja enviar email
// FIM DA FUNCAO - sendAsPdf
}
function getConfig(cell) {
var configSheet = getConfigSheet();
return configSheet.getRange(2, cell).getValue();
}
function getConfigSheet() {
var name = "script_config";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);
Logger.log("Created sheet " + name);
}
return sheet;
}
I appreciate any suggestions.
