0

I'm currently using a script that uses text from cells in google sheets to populate specific fields in a google doc which is then saved as a pdf.

I want to replace one of the fields in the google doc with an image from a cell in sheets (an auto-generated QR code), but when I use the function body.replaceText the replaced field in the output PDF contains the string 'cellimage' rather than the image.

Below is the script used:

function createBulkPDFs(){
const docFile = DriveApp.getFileById("1y6hduq3CzpM5Nr8WuhlvQNZmcKViHXNWG1zvR9KgCTk");
const tempFolder = DriveApp.getFolderById("1SLj2sldcNixIe_q8RIm_LIAON1L9rImH");
const pdfFolder = DriveApp.getFolderById("1TrhUjonuXodeZWMQMJV7ZjtN4jp8WSRH");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customs");

const data = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,20).getValues();

let errors = [];
data.forEach(row => {
  try{ 
  createPDF(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row [0],docFile,tempFolder,pdfFolder); //row numbers are to name columns //then this //row 0 is the NAMEof the PDF
   errors.push([""]);
  } catch(err) {
 errors.push(["Failed"]);
  }
}); //close forEach

currentSheet.getRange(2,21,currentSheet.getLastRow()-1,1).setValues(errors);

}

function createPDF(number,model,fors,length,width,thickness,litres,notes,blank,orders,dates,finSetup,lamination,artwork,finSystem,leash,finish,phoneNumber,shipping,qrcode,pdfName,docFile,tempFolder,pdfFolder) {


const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();
body.replaceText("{number}",number);   //name in doc on left, named column in here on right //need to do this next!!!!
body.replaceText("{model}",model);
body.replaceText("{for}",fors);
body.replaceText("{length}",length);
body.replaceText("{width}",width);
body.replaceText("{thickness}",thickness);
body.replaceText("{litres}",litres);
body.replaceText("{notes}",notes);
body.replaceText("{blank}",blank);
body.replaceText("{orderTakenBy}",orders);
body.replaceText("{dateTaken}",dates);
body.replaceText("{finSetup}",finSetup);
body.replaceText("{lamination}",lamination);
body.replaceText("{artwork}",artwork);
body.replaceText("{finSystem}",finSystem);
body.replaceText("{leash}",leash);
body.replaceText("{finish}",finish);
body.replaceText("{phoneNumber}",phoneNumber);
body.replaceText("{shippingCo}",shipping);
body.replaceText("{qrcode}",qrcode);
tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFolder.removeFile(tempFile)

Ideally the qrcode text would be replaced with an image (that is big enough to scan - maybe 150 x 150)

Can someone please help me with next steps. I am a n00b to google scripts.

skelts
  • 1
  • I seem to recal Tanaike answering a question like that. Check his answers – Cooper Nov 17 '21 at 03:48
  • See here: https://stackoverflow.com/questions/68427306/generating-pdf-succesfully-but-i-failed-when-i-added-qr-code/ or here: https://stackoverflow.com/questions/69119206/replace-text-placeholder-with-image-using-google-apps-script or here: https://stackoverflow.com/questions/20526566/insert-image-at-specific-point-of-paragraph – Yuri Khristich Nov 17 '21 at 16:28
  • And @Tanaike original example is here: https://tanaikech.github.io/2018/08/20/replacing-text-to-image-for-google-document-using-google-apps-script – Yuri Khristich Nov 17 '21 at 16:32

0 Answers0