CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
Ciaran Crowley
Ciaran Crowley

Reputation: 419

How can I force an image to appear in a pre-specified line of text using Google Apps Script?

I am writing a script that automatically extract information from a google sheet and applies it to a template made in google docs. The template includes multiple signature sections. The signatures are stored as images in a sub folder and the image file names are automatically generated by appsheet.com.

I am currently capable of extracting the image and appending it to the very end of the document, but I am unable to force the image to appear in the correct place on the document.

I can force the script to display the image name in the correct spot, but not the image. I suspect this has something to do with text wrapping, and as I am unfamiliar with Google Apps Scripts, I cannot find another way to rectify this problem.

You can find the spreadsheet containing all of the necessary data here.

The script I am writing is here.

And the document template is here.

Here is the block of code where I think the problem is located.

        var signature = row[17];
        var sign = signature.substring(signature.indexOf("/") + 1);
        var sigFolder = DriveApp.getFolderById("1IWix3MTEWnGn9lmb91XmCakz7kV3DoCV");

        var files = sigFolder.getFilesByName(sign);
        var n = 0;
        var file;
        while(files.hasNext()){
          file = files.next();
          n++;
        } if(n>1){
          SpreadsheetApp.getUi().alers('there is more than one file with this name' + sign);
        }
        var sigElectInstaller = "%SIGNELECTINSTALL%";
        //var sigElectInstaller = body.appendImage(file);
        //var sig = body.appendImage(file);
        //body.appendImage(file);
        //body.replaceText(sig, row[17]);
        //body.replaceText(file, row[17]);
        body.replaceText(sigElectInstaller, file);

The next block is the entire function.

function chooseRowMethodI(templateId, rowNumber){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var data = sheet.getRange(2, 2, 10, 41).getValues();//starting with row 2 and column 1 as our upper-left most column, get values from cells from 1 row down, and 15 columns along - hence (2,1,1,15)
  var docTitle = sheet.getRange(2, 2, 10, 1).getValues();//this is grabbing the data in field B2
  var docTitleTagNumber = sheet.getRange(2, 5, 11, 1).getValues();
  var today = new Date();
  var dd = today.getDate();
  var mm = today.getMonth() + 1;
  var yyyy = today.getFullYear();
  today = dd + '/' + mm + '/' + yyyy;

  for(var i = 0; i < values.length; i++){
    for(var j = 0; j < values[i].length; j++){
      if(values[i][j] == response){
        Logger.log(i);
        //var row = data[i - 1];
        var row = data[rowNumber];
        var docId = DriveApp.getFileById(templateId).makeCopy().getId();  
        var doc = DocumentApp.openById(docId);
        var body = doc.getActiveSection();
        //***** Script begins inserting data into document ******
        body.replaceText("%SITE%",row[0]);
        ...


        //************************  This is the section in question. Here the image is found and the attempt to insert it into the document is made ************************
        var signature = row[17];
        var sign = signature.substring(signature.indexOf("/") + 1);
        var sigFolder = DriveApp.getFolderById("1IWix3MTEWnGn9lmb91XmCakz7kV3DoCV");

        var files = sigFolder.getFilesByName(sign);
        var n = 0;
        var file;
        while(files.hasNext()){
          file = files.next();
          n++;
        } if(n>1){
          SpreadsheetApp.getUi().alers('there is more than one file with this name' + sign);
        }
        var sigElectInstaller = "%SIGNELECTINSTALL%";
        //var sigElectInstaller = body.appendImage(file);
        //var sig = body.appendImage(file);
        //body.appendImage(file);
        //body.replaceText(sig, row[17]);
        //body.replaceText(file, row[17]);
        body.replaceText(sigElectInstaller, file);
        //*********************** End of the section in question **********************

        body.replaceText("%INSTALLATIONTESTDATE%", row[18]);
        ...
        body.replaceText("%SFCDATE%", row[37]);

        doc.saveAndClose();

        var file = DriveApp.getFileById(doc.getId());
        var newFolder = DriveApp.getFolderById("1_PKmVK3EoTeMd4UStH_zUCqLw3-An5Zv");
        newFolder.addFile(file); 

        var newDocTitle = docTitle[i - 1][0];
        var newDocTagNumber = docTitleTagNumber[i - 1][0];

        doc.setName(newDocTitle + " " + newDocTagNumber + " " + today);
      }
    }
  } 
}

And this final block is what runs the script. (Please note the ui section is tabbed out for ease of use to avoid having to navigate back to the spreadsheet and manually type in a known tagNo.)

var response = "FT101";

function chooseRow(){
//  var ui = SpreadsheetApp.getUi(); // Same variations.
//  var result = ui.prompt('Please enter the Tag number of the row you wish to print.', ui.ButtonSet.OK_CANCEL);
//  
//  // Process the user's response.
//  var button = result.getSelectedButton();
//  response = result.getResponseText();
//  if (button == ui.Button.OK) {
//    // User clicked "OK".
//    ui.alert('Your tag number is' + response + '.');
//  } else if (button == ui.Button.CANCEL) {
//    // User clicked X in the title bar.
//    ui.alert('You closed the dialog.');
//    return 'the end';
//  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var category = sheet.getRange(2, 3, 11, 1).getValues();//Needs to be verified to ensure correct cell is chosen by script
  var tags = sheet.getRange(2, 5, 11, 1).getValues();//Needs to be verified to ensure correct cell is chosen by script

  for(var i = 0; i < tags.length; i++){
    if(tags[i][0] == response && category[i][0] == "Instrument"){
      var templateId = "1MaCMLoqj1cnA-GjQT3EZdoGfCsybRZ2JcL5Yi-J1p8U";
      chooseRowMethodI(templateId, i);
      return "";
    } else if(tags[i][0] == response && category[i][0] == "Motor" || tags[i][0] == response && category[i][0] == "Valve"){
      var templateId = "1cSPD23qFd-34-IIr5eJ5a5OgHp9YR6xav9T28Y4Msec";
      chooseRowMethodMV(templateId, i);
      return "";
    }
  }
}

Upvotes: 0

Views: 494

Answers (1)

Raserhin
Raserhin

Reputation: 2676

In this first block of code you have posted there is some misconception about the classes that are being involved, you are giving the method replaceText() a File resource from the drive API but it only accept string as input.

To add an image after that text I would get the paragraph element that the text is in then append the image with the insertInlineImage(). So basically your code should change from this:

    var signature = row[17];
    var sign = signature.substring(signature.indexOf("/") + 1);
    var sigFolder = DriveApp.getFolderById("1IWix3MTEWnGn9lmb91XmCakz7kV3DoCV");

    var files = sigFolder.getFilesByName(sign);
    var n = 0;
    var file;
    while(files.hasNext()){
      file = files.next();
      n++;
    } if(n>1){
      SpreadsheetApp.getUi().alers('there is more than one file with this name' + sign);
    }
    var sigElectInstaller = "%SIGNELECTINSTALL%";
    //var sigElectInstaller = body.appendImage(file);
    //var sig = body.appendImage(file);
    //body.appendImage(file);
    //body.replaceText(sig, row[17]);
    //body.replaceText(file, row[17]);
    body.replaceText(sigElectInstaller, file);

To something similar like this: 

    var signature = row[17];
    var sign = signature.substring(signature.indexOf("/") + 1);
    var sigFolder = DriveApp.getFolderById("1IWix3MTEWnGn9lmb91XmCakz7kV3DoCV");

    var files = sigFolder.getFilesByName(sign);
    var n = 0;
    var file;
    while(files.hasNext()){
      file = files.next();
      n++;
    } if(n>1){
      SpreadsheetApp.getUi().alers('there is more than one file with this name' + sign);
    }
    var sigElectInstaller = "%SIGNELECTINSTALL%";
    var targetRange = body.findText(sigElectInstaller); // Finding the range we need to focus on

    var paragraph = targetRange.getElement().getParent().asParagraph(); // Getting the Paragraph of the target
    paragraph.insertInlineImage(
                                 1, // As there are only one element in this case you want to  insert at index 1 so it will appear after the text
                                 file.getBlob() // Notice the .getBlob()
                               );
    paragraph.replaceText(sigElectInstaller, ""); // Remove the placeholder

If you want to get more control over the positioning of the image you could try to use addPositiondImage

Upvotes: 1

Related Questions