Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@kibolho
Created April 6, 2022 19:01
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kibolho/3be15d77d2a74bc3033743c36f6cb3c0 to your computer and use it in GitHub Desktop.
Save kibolho/3be15d77d2a74bc3033743c36f6cb3c0 to your computer and use it in GitHub Desktop.
function generatePDF(ssID, sheetId, fileName) {
// Base URL
const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ssID);
var rangeParam = '&r1=0' + '&r2=82' + '&c1=0' + '&c2=11'
const exportOptions =
'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' + // 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
'&scale=3' + // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
'&fzr=false' + // do not repeat row headers (frozen rows) on each page
`&gid=${sheetId}` + // the sheet's Id
'&page=1' +
// '&range=A1:L70';
rangeParam; // range
const token = ScriptApp.getOAuthToken();
// Convert individual worksheets to PDF
const response = UrlFetchApp.fetch(url + exportOptions, {
headers: {
Authorization: `Bearer ${token}`,
},
});
// convert the response to a blob and store in our array
return response.getBlob().setName(`${fileName}.pdf`);
}
function sendToBeSignInSandbox() {
sendToBeSign(true)
}
function sendToBeSign(teste=false) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheetId = ss.getId()
const sheet = ss.getActiveSheet()
const sheetId = sheet.getSheetId()
const emails = sheet.getRange("A91:C100").getValues();
const signers = emails.filter((item)=>{
return !!item[2]
}).map(item=>(
{
"name": item[1],
"email": item[2],
"action": "SIGN"
}
))
const pdfFile = generatePDF(spreadsheetId, sheetId)
var url = "https://api.autentique.com.br/v2/graphql";
var headers = { 'authorization': 'Bearer {tokenAutentique}' };
var query = `mutation CreateDocumentMutation($document: DocumentInput!,$signers: [SignerInput!]!,$file: Upload!){createDocument(sandbox: ${teste ? "true" : "false"},document: $document,signers: $signers,file: $file){id name refusable sortable created_at signatures { public_id name
email created_at action { name } link { short_link } user { id name email } } } }`;
var variables = {
"document": {
"name": "DIZIMO E OFERTAS"
},
signers,
"file": null
};
Logger.log(variables);
var operations = {
"query": query,
"variables": variables,
}
var payload = {
operations: JSON.stringify(operations),
map: JSON.stringify({
0: ["variables.file"]
}),
"0": pdfFile
}
Logger.log(payload);
var options = {
'method': 'POST',
'headers': headers,
payload,
'muteHttpExceptions': true,
}
var request = UrlFetchApp.getRequest(url, options); // (OPTIONAL) generate the request so you
Logger.log("Request payload: " + request.payload); // can examine it (useful for debugging)
var response = UrlFetchApp.fetch(url, options);
Logger.log(response);
}
function sendToConfirm() {
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<>");
const ss = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheetId = ss.getId()
const sheet = ss.getActiveSheet()
const sheetId = sheet.getSheetId()
const pdfBlob = generatePDF(spreadsheetId, sheetId, sheet.getName())
// Send the PDF of the spreadsheet to this email address
const emails = sheet.getRange("A85:A88").getValues();
const email = emails[0] || Session.getActiveUser().getEmail();
const cc = emails.slice(1, 4).join(",");
// Subject of email message
const subject = `${ss.getName()} - PDF gerado para confirmação`;
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
const body = 'Confira o PDF gerado antes de enviar para assinaturas';
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [pdfBlob],
cc
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment