Autofill a Google Docs Document With a Specific-Named Image Stored in Google Drive and Data From a S-CodePudding

I have a subscription form based on Apps Script which allows me to gather subscribers personal data and signature. When the user submits the form, data is sent to a spreadsheet and the signature is stored in a specific folder and is named with the submission ID which is the same ID of the data imported in Google Sheets.

I need to autofill a Google Docs document with data pulled from a Google Sheets spreadsheet and a specific-named image from Google Drive. Then, the Google Docs document is converted to PDF.

Here is a screenshot of the spreadsheet.

Here is a screenshot of the signature folder.

Here is a screenshot of the Google Docs template document.

The following is the code I use to generate a PDF from a Google Docs file filled with data pulled from Google Sheets spreadsheet.

function createBulkPDFs() {

const docFile = DriveApp.getFileById("1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds");
const tempFolder = DriveApp.getFolderById("1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W");
const pdfFolder = DriveApp.getFolderById("1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");

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

let errors = [];
data.forEach(row => {
  try{
  createPDF (row[2],row[3],row[7],row[8],row[4],row[9],row[12],row[10],row[11],row[5],row[6],row[13],row[1],row[0],row[2],docFile,tempFolder,pdfFolder);
  errors.push(["Completata"]);
  } catch(err){
    errors.push(["Fallita"]);
  }
});

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

}

function createPDF (name,surname,placebirth,daybirth,gender,street,zipcode,city,province,email,phone,instagram,id,timestamp,pdfName,docFile,tempFolder,pdfFolder) {
 
// ID Template Modulo di Iscrizione: 1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds
// ID tempFolder: 1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W
// ID pdfFolder: 1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK

const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();
body.replaceText("{name}", name);
body.replaceText("{surname}", surname);
body.replaceText("{placebirth}", placebirth);
body.replaceText("{daybirth}", daybirth);
body.replaceText("{gender}", gender);
body.replaceText("{street}", street);
body.replaceText("{zipcode}", zipcode);
body.replaceText("{city}", city);
body.replaceText("{province}", province);
body.replaceText("{email}", email);
body.replaceText("{phone}", phone);
body.replaceText("{instagram}", instagram);
body.replaceText("{timestamp}", timestamp);
body.replaceText("{id}", id);
tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFile.setTrashed(true);

}

Now I need to retrieve the signature image file as well and put it in the Google Docs file.

The idea is that the script should search for an image named with the specific ID and put it on the Google Docs file replacing the text placeholder {signature}.

Is there a way to achieve that?

Thanks a lot!

EDIT:

After implementing Tanaike's code suggestion, the script now successfully edits the Doc file and fills it with both data and signature image.

Nevertheless, it does not convert the file to PDF as it should.

Here is the code at the current state.

function createBulkPDFs() {

const docFile = DriveApp.getFileById("1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds");
const tempFolder = DriveApp.getFolderById("1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W");
const pdfFolder = DriveApp.getFolderById("1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");

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

let errors = [];
data.forEach(row => {
  try{
  createPDF (row[2],row[3],row[7],row[8],row[4],row[9],row[12],row[10],row[11],row[5],row[6],row[13],row[1],row[0],row[0] " " row[2] " " row[3],docFile,tempFolder,pdfFolder);
  errors.push(["Completata"]);
  } catch(err){
    errors.push(["Fallita"]);
  }
});

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

}

function createPDF (name,surname,placebirth,daybirth,gender,street,zipcode,city,province,email,phone,instagram,id,timestamp,pdfName,docFile,tempFolder,pdfFolder) {
 
// ID Template Modulo di Iscrizione: 1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds
// ID tempFolder: 1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W
// ID pdfFolder: 1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK

const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();

body.replaceText("{name}", name);
body.replaceText("{surname}", surname);
body.replaceText("{placebirth}", placebirth);
body.replaceText("{daybirth}", daybirth);
body.replaceText("{gender}", gender);
body.replaceText("{street}", street);
body.replaceText("{zipcode}", zipcode);
body.replaceText("{city}", city);
body.replaceText("{province}", province);
body.replaceText("{email}", email);
body.replaceText("{phone}", phone);
body.replaceText("{instagram}", instagram);
body.replaceText("{timestamp}", timestamp);
body.replaceText("{id}", id);

const imageFolderId = "1mdJbbG_0aF8wjEIuVPsMr9jV31wPINRk"; // Please set the folder ID of your "signatureFolder".
const filename = id; // or id
const files = DriveApp.getFolderById(imageFolderId).getFilesByName(filename);
let image;
if (files.hasNext()) {
  image = files.next().getBlob();
} else {
  throw new Error(`No file of "${filename}"`);
}
do {
  const width = 200;
  const next = body.findText("{signature}");
  if (!next) break;
  const r = next.getElement();
  r.asText().setText("");
  var img = r.getParent().asParagraph().insertInlineImage(0, image);
  if (width && typeof width == "number") {
    img.setWidth(width);
    img.setHeight(width * img.getHeight() / img.getWidth());
  }
} while (next);


tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFile.setTrashed(true);




}

CodePudding user response：

I believe your goal is as follows.

You want to replace the text of {sigunature} with an image retrieved from the folder of "signatureFolder" in the Google Document.

When your script is modified, how about the following modification? In this case, please modify the function createPDF as follows.

From:

body.replaceText("{id}", id);
tempDocFile.saveAndClose();

To:

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

// --- I added below script.
const imageFolderId = "###"; // Please set the folder ID of your "signatureFolder".
const filename = id; // or `${id}.png`;
const files = DriveApp.getFolderById(imageFolderId).getFilesByName(filename);
let image;
if (files.hasNext()) {
  image = files.next().getBlob();
} else {
  throw new Error(`No file of "${filename}"`);
}
let next;
do {
  const width = 200;
  next = body.findText("{signature}");
  if (!next) break;
  const r = next.getElement();
  r.asText().setText("");
  var img = r.getParent().asParagraph().insertInlineImage(0, image);
  if (width && typeof width == "number") {
    var w = img.getWidth();
    var h = img.getHeight();
    img.setWidth(width);
    img.setHeight(width * h / w);
  }
} while (next);
// ---

tempDocFile.saveAndClose();

Note:

Before you use this script, please set imageFolderId. This is the folder ID of your "signatureFolder".
And also, please check the filename of each image. Unfortunately, I cannot know your actual filename. So, from your showing image, I guessed that your filename might be id1.png or id1. So, please modify const filename = `${id}.png`; // or id for your actual situation.
- If the filename is id1.png, please use const filename = `${id}.png`;.
- If the filename is id1, please use const filename = id;.
If an error like No file of ### occurs, please check the filename again.
And, const width = 200; is the image size of the inserted image. Please modify this for your actual situation.