Google Apps Script Substring

Question

In my spreadsheet I have a file path to an image stored in a sub folder. The image is stored like this because I am making an app for my employer using appsheet.com. They want this app developed using AppSheet, which does not allow images to be stored directly into the spreadsheets that are used to build these app.

The data stored in the spreadsheet must also be extracted and applied to a template and I am making a Google Apps Script to do this. I have a functional script which finds the template and extract the users chosen data row, but I cannot extract the image needed.

I have been trying to make a substring of the file path to get the file name, but I have been unable to do this.

In the block of code where I am trying to create the substring, I am getting an error on the line sig = signature.getText();. The error is TypeError: signature.getText is not a function (line 176, file "Code").

This is what the file path looks like Signatures/FT101.Signed (%SIGNED%).103735.png, and the substring I need would look like this FT101.Signed (%SIGNED%).103735.png

I have tried multiple methods that were provided by other questions asked on stack overflow as well as any potential methods available in the Google Apps Script reference.

The following function takes the users input from a prompt and uses it to find the desired row number. It then takes the data from that row and applies it to a template based on the category the data falls under.

var response = {};
var sign = "";

function chooseRowMethod(templateId){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var data = sheet.getRange(2, 2, 11, 18).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, 11, 1).getValues();//this is grabbing the data in field B2
  var docTitleTagNumber = sheet.getRange(2, 3, 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 docId = DriveApp.getFileById(templateId).makeCopy().getId();

        var doc = DocumentApp.openById(docId);
        var body = doc.getActiveSection();
        body.replaceText("%SITEID%", row[0]);
        body.replaceText("%TAG%", row[1]);
        ...
        body.replaceText("%SAT%", row[14]);

        var signature = sheet.getRange(2, 18, 11, 1).getValues();
        var sig;
        var sign = {};

        for(var i = 0; i < values.length; i++){
          for(var j = 0; j < values[i].length; j++){
            if(values[i][j] == response){
              sig = signature.getText();
              sign[i][0] = sig.substring(sig.indexOf("/") + 1);
            }
          }
        }

        var sigFolder = DriveApp.getFolderById("1LiJKGjTbpvRZ5RrMTQoyTuAjrozA14FN");

        var file = sigFolder.getFilesByName(sign);
        var image = file.getId();

        body.appendImage(image);
        doc.saveAndClose();

        var file = DriveApp.getFileById(doc.getId());
        var newFolder = DriveApp.getFolderById("16wRGBVdV0OZ5YfKhqEQSFMsux-ekGCCa");
        newFolder.addFile(file); 
        var newDocTitle = docTitle[i - 1][0];
        var newDocTagNumber = docTitleTagNumber[i - 1][0];

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

This is where I have been attempting to get the image.

        var signature = sheet.getRange(2, 18, 11, 1).getValues();
        var sig;
        var sign = {};

        for(var i = 0; i < values.length; i++){
          for(var j = 0; j < values[i].length; j++){
            if(values[i][j] == response){
              sig = signature.getText();
              sign[i][0] = sig.substring(sig.indexOf("/") + 1);
            }
          }
        }

        var sigFolder = DriveApp.getFolderById("1LiJKGjTbpvRZ5RrMTQoyTuAjrozA14FN");

        var file = sigFolder.getFilesByName(sign);
        var image = file.getId();

        body.appendImage(image);

This next function gives the user the prompt and applies the correct template.

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);

  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, 4, 11, 1).getValues();
  var templateId = {};

  for(var i = 0; i < values.length; i++){
    for(var j = 0; j < values[i].length; j++){
      if(values[i][j] == response && category[i - 1][0] == "Instrument"){
        templateId = "1cx2-6ju-o7DaRPnbuYxxdvVVFeGQzpTXaXV3wMuRpqo";
        chooseRowMethod(templateId);
        return "";
      } else if(values[i][j] == response && category[i][0] == "Motor" || values[i][j] == response && category[i][0] == "Valve"){
        templateId = "1sYx_JcoDHY-pzjEDlxMMa3dtdzOOE8CyyLGQk8WHg7s";
        chooseRowMethod(templateId);
        return "";
      }
    }
  }
}

The expected result is a substring of the file path that can be used to retrieve an image that can be appended to the body of a document.

Here is a link to the spreadsheet.

Solution

I did several changes to your code, including the edit proposed by Cooper. It's retrieving the substring and adding the image to the file successfully. I hope it works for you:

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);
  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 tags = sheet.getRange(2, 4, 11, 1).getValues();
  var category = sheet.getRange(2, 3, 11, 1).getValues();
  for(var i = 0; i < tags.length; i++){
    if(tags[i][0] == response && category[i][0] == "Instrument"){
      var templateId = "my_template_id";
      chooseRowMethod(templateId, i);
      return ""; // You don't need to return empty string, just return null
    } else if(tags[i][0] == response && category[i][0] == "Motor" || tags[i][0] == response && category[i][0] == "Valve"){
      var templateId = "my_template_id_bis";
      chooseRowMethod(templateId, i);
      return ""; // You don't need to return empty string, just return null
    }
  }
}

The function chooseRowMethod gets the row index chosen by the user in chooseRow so that it doesn't have to be looked for again:

function chooseRowMethod(templateId, rowNumber){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2, 2, 11, 18).getValues();
  var today = new Date();
  var dd = today.getDate();
  var mm = today.getMonth() + 1;
  var yyyy = today.getFullYear();
  today = dd + '/' + mm + '/' + yyyy;

  var row = data[rowNumber];
  var docTitle = row[1];
  var docTitleTagNumber = row[2];
  var docId = DriveApp.getFileById(templateId).makeCopy().getId();
  var doc = DocumentApp.openById(docId);
  var body = doc.getActiveSection();
  body.replaceText("%SITEID%", row[0]);
  body.replaceText("%TAG%", row[1]);
  // ...
  body.replaceText("%SAT%", row[14]);

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

  var sigFolder = DriveApp.getFolderById("my_sigfolder_id");
  var files=sigFolder.getFilesByName(sign);
  var n = 0;
  while(files.hasNext()) {
    var file=files.next();
    n++;
  } if(n>1) {
    SpreadsheetApp.getUi().alert('There is more than one file with this name: ' + sign);
  }
  body.appendImage(file);
  doc.saveAndClose();

  var file = DriveApp.getFileById(doc.getId());
  var newFolder = DriveApp.getFolderById("my_newfolder_id");
  newFolder.addFile(file); 
  doc.setName(docTitle + " " + docTitleTagNumber + " " + today);
}

Comments

[I hope this is helpful to you]

Can you share a sanitized copy of your Spreadsheet? It would greatly help to understand and solve your issue.

[ross]

Spreadsheet link is private.