0

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);

  }
}
Ryan May
  • 1
  • 1
  • 1
    Can you see if the answer of this [post](https://stackoverflow.com/questions/63413712/can-no-longer-produce-pdf-from-google-sheet-with-some-users/63414460#63414460) answers your question. Essentially, replace your current url_base with var `url_base= 'https://docs.google.com/spreadsheets/d/'+SpreadsheetApp.getActiveSpreadsheet().getId()+'/';` – Marios Aug 15 '20 at 22:34
  • Marios I tried that but it says "Exception: Request failed for https://docs.google.com returned code 400. Truncated server response: – Ryan May Aug 22 '20 at 02:15
  • [Edit] your question to show the modifications, the modified script , error messages and show why it is not a duplicate to warrant reopening. – TheMaster Aug 22 '20 at 02:23
  • Ryan To address a user, use `@` like, @Marios – TheMaster Aug 22 '20 at 13:58
  • @Marios I tried your suggestion and got another error message. I have edited the script above to reflect it. You will see I commented what I originally had for url_base. The error I received is 3 posts up from this one – Ryan May Aug 25 '20 at 19:04
  • @RyanMay you have more than 5 SyntaxErrors. Please correct them first and then update your code. – Marios Aug 25 '20 at 19:33
  • 1
    @Marios I apologize, I have two almost identical scripts for testing purposes and I got confused and used the wrong one with your solution. I have now implemented the url_base you suggested in the correct script and it is working. Thank you so much! Can you please explain to me why that worked and why the url_base I had suddenly stopped working? – Ryan May Aug 26 '20 at 17:10

0 Answers0