0

Have collected info through a google form to a google sheet. Part of that info is the path to an image stored on drive. I have an app script that replaces key words on a document with the data collected in the sheet. I can get it to replace a piece of text, {{image}} for example with the url stored but I cannot get it to actually put a copy of the image into the document.

Any suggestions. Code below

// @ts-nocheck
function autoFillGoogleDocFromForm(e) {
  
  //e.values is an array of form values
  var timestamp = e.values[0];
  var email = e.values[1];
  var who = e.values[2];
  var employeeorcon = e.values[3];
  var location = e.values[4];
  var roomorarea = e.values[5];
  var type = e.values[6];
  var dateofworks = e.values[7];  
  var imageofcompletedcon = e.values[8];
  var checkedoffintext = e.values[9];
  var checkname = e.values[10];
  var checkco = e.values[11];
  var checkeddate = e.values[12];
 

  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById("1WcYKvsRFbKK73J-ep66mR9drZyrkWap-x30rO-kVUcM"); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById("1MeU3-N3BMqOPvoaSGr2XassibR2XajdN")
  var copy = file.makeCopy(roomorarea + '_' + timestamp, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody(); 
  
  //Then we call all of our replaceText methods
  body.replaceText('{{location}}', location); 
  body.replaceText('{{room}}', roomorarea);   
  body.replaceText('{{completedby}}', who); 
  body.replaceText('{{checkedby}}', checkname); 
  body.replaceText('{{checkeddate}}', checkeddate);  
  body.replaceText('{{insdate}}', dateofworks); 
  body.replaceText('{{Empcon}}', employeeorcon);  
  body.replaceText('{{Type}}', type); 
  body.replaceText('{{image}}', imageofcompletedcon); 
  body.replaceText('{{methchk}}', checkedoffintext);  
  body.replaceText('{{checker}}', checkname); 
  body.replaceText('{{cocheck}}', checkco);   
  body.replaceText('{{datecheck}}', checkeddate); 
  
  
    
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 
}

New to this, but tried insertimage etc, but really a bit beyond me.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Kevin Greenwell
  • 1
  • What does `it's a bit beyond me mean`. It sound likes there is a better question to ask there. – Cooper Nov 11 '22 at 16:28
  • Sorry, what I meant was, I have read through various posts on trying to get an image inserted into a table cell or sheet cell, but nothing tends to work, I'm stumped. Can anybody help. – Kevin Greenwell Nov 11 '22 at 16:41
  • I'm a bit mixed up by your question. Do you wish to insert the image into a google doc or google sheet? – Cooper Nov 11 '22 at 16:52
  • Into a google doc. I have a table, and in that table I have put refarence words to lookup and replace. I am using var imageofcompletedcon = e.values[8]; to grab what ever is in column 8 and place it in variable imageofcompetedcon then using body.replaceText('{{image}}', imageofcompletedcon); to look for the text {{image}} in the body of the document and replace it with whatever is in imageofcompletedcon. That puts the breadcrumb link to the image ok, what I want if possible is to display the actual image. – Kevin Greenwell Nov 11 '22 at 23:10
  • If your using the event object of a form submission e.values[8] is actually column9. e.values[0] is a timestamp and it's in column 1 – Cooper Nov 11 '22 at 23:14
  • thanks, I am getting the link to the image submitted as its in column 7 of the sheet so script has it in e.values[8]. I can place e.values[8] into the document by using body.replaceText . What I want is for the script to use that link to place the image in the document. – Kevin Greenwell Nov 12 '22 at 09:14
  • Column7 is e.values[6] – Cooper Nov 12 '22 at 15:45
  • apologise, I counted wrong. the e.value[8] is for column So that part is correct. Thinking about it, I should go to that file , get its ID, and insert image by that ID. – Kevin Greenwell Nov 12 '22 at 17:49
  • The reason there is a difference is that arrays start indexing at zero and columns start at 1 so the column number will alway be one greater than the array index – Cooper Nov 12 '22 at 19:37
  • I understand that and thanks for the clarrification. I can get the path to an image into the sheet ok, and reference to it correctly in Apps Script, what I can not do is get a blob of that image into the document. Thats what i am stuck on and why Im asking for help – Kevin Greenwell Nov 13 '22 at 07:56
  • Does this help: https://stackoverflow.com/questions/66782960/inserting-images-into-a-sheet-as-blobsource-using-google-apps-script – Cooper Nov 13 '22 at 16:29

0 Answers0