how to input newly created pdf link into google sheet column

Question

I tried multiple ways, but for a few days, none worked for me.

This is my script. I manage to create the doc and also the pdf. I inputted the document link but what I want is a pdf link. I couldn't get the getUrl function right for my pdf created.

Can anyone just let me know what I should put in? Many thanks in advance.

 function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Create Form');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}
function createNewGoogleDocs() {

  const googleDocTemplate = DriveApp.getFileById('1wROa5kWXGvsOSaeb_34ncF_vcbWA4SFXGuXkwCqjAW0');
  const destinationFolder = DriveApp.getFolderById('1qld3qQDQNtaGdoOxQCsSre1VjWQ6NKGn')
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Sheet1')
  
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index){

    if (index === 0) return;
    if (row[23]) return;

    const copy = googleDocTemplate.makeCopy(`${row[1]} - ${row[2]} Order Form` , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[18]).toLocaleDateString();

    body.replaceText('{{Submission Date}}', row[0]);
    body.replaceText('{{Case ID}}', row[1]);
    body.replaceText('{{Name}}', row[2]);
    body.replaceText('{{Contact Number}}', row[3]);
    body.replaceText('{{Main Service}}', row[4]);
    body.replaceText('{{Type}}', row[5]);
    body.replaceText('{{Brand}}', row[6]);
    body.replaceText('{{Model}}', row[7]);
    body.replaceText('{{IMEI No. Or Serial No.}}', row[8]);
    body.replaceText('{{Warranty}}', row[9]);
    body.replaceText('{{Password/Pattern}}', row[10]);
    body.replaceText('{{Format}}', row[11]);
    body.replaceText('{{Include Parts}}', row[12]);
    body.replaceText('{{Issues}}', row[13]);
    body.replaceText('{{Full Address}}', row[14]);
    body.replaceText('{{Choose One}}', row[15]);
    body.replaceText('{{Details}}', row[16]);
    body.replaceText('{{Status}}', row[17]);
    body.replaceText('{{Collection Date}}', friendlyDate);
    body.replaceText('{{Special Case Reject Reason}}', row[19]);
    body.replaceText('{{Quotation}}', row[20]);
    body.replaceText('{{Collection Date}}', row[21]);
    body.replaceText('{{Installation Date}}', row[22]);

    doc.saveAndClose();

const pdfContentBlob = doc.getAs(MimeType.PDF);
    DriveApp.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`);

const url = doc.getUrl();
    sheet.getRange(index + 1, 24).setValue(url)
    
    })}

when I put in pdfContentBlob there's no geturl function. I am not good at this at all. I hope to have someone able to give me the line of codes I just need to put in to make it work.

Thanks!

Answer 1

In your script, const url = doc.getUrl(); is used as the URL. In this case, the URL is for Google Document. When you want to retrieve the URL of the PDF file, how about the following modification?

From:

    DriveApp.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`);

const url = doc.getUrl();

To:

const url = DriveApp.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`).getUrl();

• If you want to save the PDF file to the folder of destinationFolder, please modify it as follows.

    const url = destinationFolder.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`).getUrl();
  

Note:

• In your script, setValue is used in a loop. In this case, the process cost becomes a bit high. If you want to reduce the process cost, I think that the following modification might be able to be used.

function createNewGoogleDocs() {
  const googleDocTemplate = DriveApp.getFileById('###'); // Please set your document ID.
  const destinationFolder = DriveApp.getFolderById('###'); // Please set your folder ID.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  const [, ...rows] = sheet.getDataRange().getValues();
  const values = rows.map(function (row) {
    if (row[23]) return [row[23]];
    console.log("Passed1")
    const copy = googleDocTemplate.makeCopy(`${row[1]} - ${row[2]} Order Form`, destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[18]).toLocaleDateString();
    body.replaceText('{{Submission Date}}', row[0]);
    body.replaceText('{{Case ID}}', row[1]);
    body.replaceText('{{Name}}', row[2]);
    body.replaceText('{{Contact Number}}', row[3]);
    body.replaceText('{{Main Service}}', row[4]);
    body.replaceText('{{Type}}', row[5]);
    body.replaceText('{{Brand}}', row[6]);
    body.replaceText('{{Model}}', row[7]);
    body.replaceText('{{IMEI No. Or Serial No.}}', row[8]);
    body.replaceText('{{Warranty}}', row[9]);
    body.replaceText('{{Password/Pattern}}', row[10]);
    body.replaceText('{{Format}}', row[11]);
    body.replaceText('{{Include Parts}}', row[12]);
    body.replaceText('{{Issues}}', row[13]);
    body.replaceText('{{Full Address}}', row[14]);
    body.replaceText('{{Choose One}}', row[15]);
    body.replaceText('{{Details}}', row[16]);
    body.replaceText('{{Status}}', row[17]);
    body.replaceText('{{Collection Date}}', friendlyDate);
    body.replaceText('{{Special Case Reject Reason}}', row[19]);
    body.replaceText('{{Quotation}}', row[20]);
    body.replaceText('{{Collection Date}}', row[21]);
    body.replaceText('{{Installation Date}}', row[22]);
    doc.saveAndClose();
    const pdfContentBlob = doc.getAs(MimeType.PDF);
    const url = DriveApp.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`).getUrl(); // or const url = destinationFolder.createFile(pdfContentBlob).setName(`${row[1]} - ${row[2]} Order Form`).getUrl();
    return [url];
  });
  sheet.getRange(2, 24, values.length).setValues(values);
}