Insert image from sheets into docs using GAS

[Varun Mustyala]

Hi All,

How do I add an image generated through a formula in a google sheet cell to a word doc which in turn can be converted to a PDF.

Got through some part of it through some videos but stuck at the above issue.

An help would be appreciated.

function createBulkPDFs(){

const docFile = DriveApp.getFileById("1GHJc4Vxk76AFMh-66aVHC4ffwtuJ0YULhuKkNDJQ8x8");

const tempFolder = DriveApp.getFolderById("1yKUILQgE_LVTXO0tWok4v4nynhbxp5G2");

const pdfFolder = DriveApp.getFolderById("1PSRbleOjn26k1H8cCworU29DiQTLvucW");

const currentSheet = SpreadsheetApp.openById("1LxuyVuoCEaNVHX0AA2r3sXSd_Hd2oUgqYvVajtQJ3dg").getSheetByName("Test");

const data = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,currentSheet.getLastColumn()).getDisplayValues();

data.forEach(row => {

  const PCB_Tracking_No = row[0];

  const Cust_Name = row[2];

  const App_type = row[4];

  const LT_Link = row[12];

  const Good_Practices = row[14];

  const pdfName = row[0];

  const LT_QR = row[16];

createPDF(PCB_Tracking_No,Cust_Name,App_type,LT_Link,Good_Practices,LT_QR,docFile,tempFolder,pdfFolder,currentSheet,pdfName);

});

}

function createPDF(PCB_Tracking_No,Cust_Name,App_type,LT_Link,Good_Practices,LT_QR,docFile,tempFolder,pdfFolder,currentSheet,pdfName) {

 

  const tempFile = docFile.makeCopy(tempFolder);

  const tempDocFile = DocumentApp.openById(tempFile.getId());

  const body1 = tempDocFile.getBody();

  const body = tempDocFile.getBody();

  body.replaceText("{PCB_Tracking_No}",PCB_Tracking_No);

  body.replaceText("{Cust_Name}",Cust_Name);

  body.replaceText("{App_type}",App_type);

  body.replaceText("{LT_Link}",LT_Link);

  body.replaceText("{Good_Practices}",Good_Practices);

body.replaceText("{LT_QR}",LT_QR);

 

  tempDocFile.saveAndClose();

  const pdfContentBlob = tempFile.getAs(MimeType.PDF);

  pdfFolder.createFile(pdfContentBlob).setName(pdfName);

  tempFolder.removeFile(tempFile);

 

}

QR formula is dependent on columns M drive link

formula = image(CONCATENATE("https://api.qrserver.com/v1/create-qr-code/?size=300x300&data=", ENCODEURL(M2)))