0

I am looking for an app script that lets me save a sheet as PDF from my Google Workbook to a specific folder in Google Drive based on a condition.

In my Google Drive I have a Google Sheet Workbook file and an employee folder. The employee folder Id is 1Jy2Yr7u0qrgy90Gvtb6p8V27baS7T_eP

My google workbook has a sheet called 'Sheet2' whose Id is 1DmDBmEijUkolVp7aVgGNK4cTRu5y4uLdzbCrDYO5XRA. This sheet has a cell C3 that has an employee name. The value in this cell needs to be checked, i.e. if the employee name matches then the script should convert the sheet to a PDF format and place the PDF in employee folder.

I am not a programmer or a coder but doing my best to get this working. I went through many scripts that I found here in stackoverflow and in Youtube and came up with this. I am not sure if it is correct. Need some expert help. Hoping someone can help.


function checkSheet() {
var sheetName = "1DmDBmEijUkolVp7aVgGNK4cTRu5y4uLdzbCrDYO5XRA";
var folderID = "1Jy2Yr7u0qrgy90Gvtb6p8V27baS7T_eP";

var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var folder = DriveApp.getFolderById(folderID);


var checkForNews = spreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracking").getRange("c6").getValue();
if (checkForNews='Yes') {
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder));
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
}
}

Here are the resource that I found,

Using Google Apps Script to save a single sheet from a spreadsheet as pdf in a specific folder

How to check for cell value?

How can I modify this to save a spreadsheet into a new folder

As mentioned above I came up with that script which I think is not right. Please help.

Shan Jose
  • 15
  • 3
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `if the employee name matches then the script should convert the sheet to a PDF format and place the PDF in employee folder.`. Can I ask you about the detail of your expected result? – Tanaike Feb 03 '23 at 00:00

1 Answers1

0

Try it this way:

function checkSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Tracking");
  var fldr = DriveApp.getFolderById("folder id");
  if(sh.getRange("C6").getValue() == "Yes") {
    var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
    fldr.createFile(theBlob);
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you so much Cooper. This did the trick. However, it is saving the entire workbook as a PDF file and not that specific sheet. Also, if I want to give the PDF file a specific name how do I do that? For example, the PDF name format should be 'Employee Name_Case#' (where Employee Name is the value in cell C6 and Case# is the value in cell D6) – Shan Jose Feb 03 '23 at 14:35
  • I got the file renaming to work. I used the Concatenate formula to join the values in the two cells and then used the following in the script, var fileName = ss.getSheetByName("Sheet2").getRange("G1").getValue(); and changed .setName("mypdf") to .setName(fileName). I am still stuck with first issue where the entire workbook is getting saved and not that specific sheet. – Shan Jose Feb 03 '23 at 15:29
  • The new file is stored in fldr and you provide the folder id – Cooper Feb 06 '23 at 02:04