0

I have a code that, upon a submission from a linked form, will select the row with the most recently submitted information, which auto-fills another sheet in the format I prefer and then saves that as a PDF. I have used this code for several worksheets but now I have need of it on a sheet that contains an image. My PDF is saving without the image, which is essential to the process. In addition to this, I would also like it to save in landscape if someone could help with that, I'd appreciate it.

I've played around with the code a bit, but I had help with writing it and don't understand the language enough to make this work.

function generatePdf() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheeta = ss.getSheetByName('Firstsheet');
sheeta.getRange("A2:A").clear();
var lastrow = sheeta.getLastRow();
var range = sheeta.getRange(lastrow, 1);
var values = range.setValue("autofill"); //This is a checkbox in column A which triggers the vlookup on the second sheet

var originalSpreadsheet = SpreadsheetApp.getActive();

var sourcesheet = originalSpreadsheet.getSheetByName("Secondsheet");
var sourcerange = sourcesheet.getRange('A:I');
var sourcevalues = sourcerange.getValues();
var data = sourcesheet.getDataRange().getValues();
var pdfname = sourcesheet.getRange('E34').getDisplayValue();

var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var projectname = SpreadsheetApp.getActiveSpreadsheet();
var sheet = sourcesheet.copyTo(newSpreadsheet);
var destrange = sheet.getRange('A:I');
destrange.setValues(sourcevalues);
newSpreadsheet.getSheetByName('Sheet1').activate();
newSpreadsheet.deleteActiveSheet();

var pdf = DriveApp.getFileById(newSpreadsheet.getId());
var theBlob = pdf.getBlob().getAs('application/pdf').setName("Sheet" + pdfname);

var folderID = "folder ID goes here";
var folder = DriveApp.getFolderById(folderID);
var newFile = folder.createFile(theBlob);

DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);

sheeta.getRange("A2:A").clear();
}

I need the image in A1:F29 (merged) to save into the intermediary sheet that this formula creates to then save to the PDF. It would also be nice to save in landscape if at all possible.

Community
  • 1
  • 1
NMALM
  • 378
  • 2
  • 19
  • I think that in order to correctly understand about your situation, providing a sample Spreadsheet will help users think of your issue and the solution. Of course, please remove your personal information. – Tanaike Jul 15 '19 at 22:32
  • [The may help with the Landscape issue](https://ctrlq.org/code/19869-email-google-spreadsheets-pdf) – Cooper Jul 15 '19 at 23:55
  • I was thinking the same thing after I asked this. Here is the form: https://docs.google.com/forms/d/1jsayFbXSH8kWkFIQt3-_QwRavJT545n44T6l0QtwPlA/edit?usp=sharing And here is the sheet: https://docs.google.com/spreadsheets/d/1ddJC6gx61VDzqPLsMxrGkQU7R4_93LsGLF0pkZgTfsw/edit?usp=sharing – NMALM Jul 15 '19 at 23:56

1 Answers1

1

1) The problem with the missing image is that you’re making the copy process twice, the first one using copyTo() function which copy the all sheet correctly. And then a second one where you use:

  var destrange = sheet.getRange('A:I');
  destrange.setValues(sourcevalues);

Which copy all the data, even “over the cell” images but not “in cell” images (probably because this is a new Sheets feature), which probably is the problem you’re facing. So you should delete those 2 lines of code in order to not override the first copy process. That’s what i did and worked fine.

2) As there is no option to specify the landscape feature, you can use the method they used in the link provided by @cooper [1] making a request to the export Url. I implemented the code and worked as intended, you just have to erase these 2 lines:

  var pdf = DriveApp.getFileById(newSpreadsheet.getId());
  var theBlob = pdf.getBlob().getAs('application/pdf').setName("Sheet" + pdfname);

For this:

var url = newSpreadsheet.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/, '');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
    //below parameters are optional...
    '&portrait=false' + //orientation, false for landscape
    '&gid=' + newSpreadsheet.getSheetId(); //the sheet's Id

  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url + url_ext, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });

  var theBlob = response.getBlob().setName("Sheet" + pdfname);

[1] https://ctrlq.org/code/19869-email-google-spreadsheets-pdf

Andres Duarte
  • 3,166
  • 1
  • 7
  • 14
  • The landscape worked perfectly, thank you. You are correct about the image, however, if I make the suggested change, my information doesn't show up. It looks to me like those lines are to copy over the formresponses, so the secondary sheet can read those. I think if we made this part reference the formresponses sheet solely, that would prevent it from overwriting the information on the PDF sheet. I think you pointed me in the right direction. Thank you, Andres! – NMALM Jul 16 '19 at 18:56
  • I just manipulated the ranges to work around the picture and it worked just fine. Thank you again for pointing me in the right direction. – NMALM Jul 16 '19 at 19:32