I have a script that is supposed to create and send invoices every time a form is submitted. It worked great up until last week when all the PDF files started it sends were corrupt. It works fine on my other Google account, but this one it seems no matter what I try they still send corrupt files. I will attach my code
function invoices() {
var iSS = SpreadsheetApp.openById("1jozNdq0Cs3aDjQ6OxY9QlkfrafDtF8Cy3VmtrxIlfP8");
var deleted = iSS.getSheetByName('Invoice')
iSS.deleteSheet(deleted)
var temp = iSS.getSheetByName('Template')
temp.copyTo(iSS)
var rename = iSS.getSheetByName('Copy of Template');
rename.setName('Invoice')
var iS = iSS.getSheetByName('Invoice');
var calcSS = SpreadsheetApp.openById("1743Slg7tHISIO1kQsVWRAX9y-CSYk00SJIbaF02BlBs");
var calcS = calcSS.getSheetByName('Calendar');
var getName = calcS.getRange("B2").getValue();
iS.getRange("B3").setValue(getName)
var invNumGet = calcS.getRange("K2").getValue();
var invNumSet = iS.getRange("F12");
invNumSet.setValue(invNumGet)
var dataRange = calcSS.getSheetByName('TeacherReference').getRange("Y2").getValue();
// var rowRange = calcSS.getSheetByName('TeacherReference').getRange(1, 1,
dataRange.getLastRow()).getValues();
//iS.getRange("E4").setValue(dataRange+4)
for (var i=1;i<dataRange+4;i++) {
if (calcSS.getSheetByName('TeacherReference').getRange(i,1).getValue()==getName){
var add = SpreadsheetApp.openById("1HFpKxSxBPeFMvWMBCjTPX6YvceT_raReV8A8- fiumgA").getSheetByName('Teacher Master List').getRange(i,15)
var added = add.getValue();
add.setValue(added + 1)
var address = calcSS.getSheetByName('TeacherReference').getRange(i,4).getValue();
iS.getRange("B4").setValue(address)
var phone = calcSS.getSheetByName('TeacherReference').getRange(i,7).getValue();
iS.getRange("B6").setValue(phone)
var email = calcSS.getSheetByName('TeacherReference').getRange(i,11).getValue();
iS.getRange("B7").setValue(email)
var fullName = calcSS.getSheetByName('TeacherReference').getRange(i,13).getValues();
iS.getRange("B3").setValue(fullName)
}
}
var startDate = calcS.getRange("E2").getValue();
iS.getRange("G14").setValue(startDate)
var endDate = calcS.getRange("F2").getValue();
iS.getRange("G15").setValue(endDate)
var Travel = calcS.getRange("H2").getValue();
iS.getRange("G25").setValue(Travel);
var getRow = calcS.getDataRange();
var getRange = calcS.getRange(5,2,getRow.getLastRow()).getValues();
var getRowRange = getRange.length;
var rowRange = getRowRange-4
iS.insertRows(20,rowRange)
iS.getRange(19,2,rowRange+4).setValues(getRange)
var dates = calcS.getRange(5,5,getRow.getLastRow()).getValues();
iS.getRange(19,5,rowRange+4).setValues(dates)
var hours = calcS.getRange(5,7,getRow.getLastRow()).getValues();
iS.getRange(19,6,rowRange+4).setValues(hours)
var totalPS = calcS.getRange(5,9,getRow.getLastRow()).getValues();
iS.getRange(19,7,rowRange+4).setValues(totalPS)
sendInvoice()
}
function sendInvoice(){ // this is the function to call
var ss = SpreadsheetApp.openById("1jozNdq0Cs3aDjQ6OxY9QlkfrafDtF8Cy3VmtrxIlfP8");
var sh = ss.getSheets()[1]; // it will send sheet 1 which is the second sheet in the
spreadsheet.
// if you change the number, change it also in the parameters below
var shName = sh.getName()
sendSpreadsheetToPdf(1, shName, sh.getRange('B15').getValue()+",
"+sh.getRange('B7').getValue(),"Invoice for "+sh.getRange("E3").getValue(),"Date Range
from
"+sh.getRange('G14').getValue()+" to "+sh.getRange('G15').getValue());
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
var spreadsheet =
SpreadsheetApp.openById("1jozNdq0Cs3aDjQ6OxY9QlkfrafDtF8Cy3VmtrxIlfP8");
var spreadsheetId = spreadsheet.getId()
var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
//var url_base = spreadsheet.getUrl().replace(/edit$/,'');
var url_base = 'https://docs.google.com/spreadsheets/d/'+SpreadsheetApp.getActiveSpreadsheet().getId()+'/';
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=A4' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=true&printtitle=false&pagenumbers=true' //hide optional headers and
footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
var options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName(pdfName + '.pdf');
if (email) {
var mailOptions = {
attachments:blob, htmlBody:htmlbody
}
MailApp.sendEmail(
email,
subject+" (" + pdfName +")",
"html content only",
mailOptions);
}
}