Insert image from sheets into docs using GAS

[Misael Rodriguez]

Hi experts, 

As the title states, I need to Insert various images from sheets into docs using GAS to create a "Ticket".

this is the code I have so far... but I just can't get it to work. Any help would be appreciated.

function createBulkPDFs(){

 const docFile = DriveApp.getFileById("1hjNxk3f5beqXOgo8PQUfQEZnwbvreGP0tKTjhAExaQ4");

 const tempFolder = DriveApp.getFolderById("1GlJHRbupM0j-7J4lYiViiNQWB8gP_S_O");

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

 const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("People");

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

 

 let errors = [];

 data.forEach(row => {

   try{

    createPDF(row[0],row[1],row[2],row[3],row[0] + " " + row[1],docFile,tempFolder,pdfFolder);

    errors.push([""]);

   } catch(err){

     errors.push(["Failed"]);

   }

 });

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

}

function createPDF(firstName,uniqueID,funcion,qrCode,pdfName,docFile,tempFolder,pdfFolder,currentSheet) {

 

 const tempFile = docFile.makeCopy(tempFolder);

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

 const body = tempDocFile.getBody();

 body.replaceText("{first}", firstName);

 body.replaceText("{unique}", uniqueID);

 body.replaceText("{qrcode}", qrCode);

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

 

 // Convert image to blob

 const range = currentSheet.getRangeByName("qrcode");

 const cellImage = range.getValues();

 const blob = cellImage.getBlob();

 body.insertImage(0, blob);

   

 tempDocFile.saveAndClose();

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

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

 tempFolder.removeFile(tempFile);

}

[Tanaike]

I think that in your situation, it is required to know the detail of images in your actual Spreadsheet. How were your images put into the cells?

[Misael Rodriguez]

Hi, thank you for your reply... 

They are both being put into the cells via the IMAGE Function

=IMAGE("URL")

[Tanaike]

Thank you for replying. From your reply, in this case, I think that you can retrieve the URL using this sample script https://stackoverflow.com/q/23750321

When the URL is retrieved, you can retrieve the image blob using UrlFetchApp.

[Misael Rodriguez]

Thank you for trying to help me, I still can't figure it out lol. I will continue to try.

[Misael Rodriguez]

Here is access to the spreadsheet (its a test anyway)

https://docs.google.com/spreadsheets/d/1ov8h-qqD2_M_aF00ooNQyvixa5s3Vm-nd3s2rZI3Ozg/edit?usp=sharing

and the files

https://drive.google.com/drive/folders/1Qt3DgzNX8bgGiuxDPfDcFqqyUy21FGTS?usp=sharing

In case you have the time to look through... thanks in advance!

[Tanaike]

Thank you for replying. When I saw your sample Spreadsheet and your script, in this case, it is required to retrieve both the values and formulas. Because your formula is like `=IF(ISBLANK(#REF!)," ",IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=312x312&chl="&B2))`.

In order to retrieve the URLs from your Spreadsheet, a simple sample script is as follows.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("People");
const lastRow = sheet.getLastRow();
const values = sheet.getRange("B2:B" + lastRow).getValues();
const formulas = sheet.getRange("D2:D" + lastRow).getFormulas();
const urls = values.map(([b], i) => formulas[i][0].match(/IMAGE\("(.+)"/)[1] + b);
console.log(urls)

By this, the URLs for retrieving the QR codes can be retrieved. If this was not useful, I apologize.

[Misael Rodriguez]

Thank you so much for your help... and I thought I had it all figured out :D

But if I could impose on you (hopefully one last time). I am just very new at this scripting.

I was able to get the image to show in my Doc and ultimately create the PDF, but I noticed two things (and I tried to resolved them, but I just couldn't figure it out)

1. The image goes above the table, not where I thought it would go (and it creates a two page document)

2. It puts the same QR Code image in all of them (not each unique one)

Thank you in advance and I really appreciate all the help so far (I am learning quite a bit).

[Tanaike]

Thank you for replying. I'm glad your issue was resolved.