"TypeError: Cannot read property 'substring' of undefined" while using two substrings in two different functions

Question

I have images which are stored as file paths in a spreadsheet. The image is extracted via Google Apps Script by creating a substring of their file path name and using it to find the image in a pre specified folder. I to extract 3 signatures per data set, but I am only able to extract one.

function motorElectInstallSignature(row, body){
  var signature = row[22];
  var sign = signature.substring(signature.indexOf("/") + 1);
  var sigFolder = DriveApp.getFolderById("16C0DR-R5rJ4f5_2T1f-ZZIxoXQPKvh5C");
  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, file.getBlob());// As there are only one element in this case you want to  insert at index 1 so it will appear after the text // Notice the .getBlob()
  paragraph.replaceText(sigElectInstaller, ""); // Remove the placeholder
}

This function works as intended with no errors. This next function is where the error occurs.

function motorCommEngineerSig(row, body){
  var signature1 = row[69];
  var signCommEng = signature1.substring(signature1.indexOf("/") + 1);
  var sigFolder = DriveApp.getFolderById("16C0DR-R5rJ4f5_2T1f-ZZIxoXQPKvh5C");
  var files = sigFolder.getFilesByName(signCommEng);
  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' + signCommEng);
  }
  var sigCommEng = "%SFCE%";
  var targetRange = body.findText(sigCommEng); // Finding the range we need to focus on
  var paragraph = targetRange.getElement().getParent().asParagraph(); // Getting the Paragraph of the target
  paragraph.insertInlineImage(1, file.getBlob());// As there are only one element in this case you want to  insert at index 1 so it will appear after the text // Notice the .getBlob()
  paragraph.replaceText(sigCommEng, ""); // Remove the placeholder
}

The error occurs on this line:var signCommEng = signature1.substring(signature1.indexOf("/") + 1); As you can see, there is no difference between both functions, with the exception of some variable names and a row number. These functions work as intended multiple times in a script that I have previously written. These functions serve the same purpose here as they did in my previous script and they are written the exact same way.

The speradsheet can be found here

The Script can be found here

Sounds like an issue with the input data. Can you provide some sample data where you encounter the error? — Adam Stevenson
– Adam Stevenson, Commented Dec 2, 2019 at 15:49
I have added the spreadsheet where the data is contained. Thank you for any help you can provide. @AdamStevenson — Ciaran Crowley
– Ciaran Crowley, Commented Dec 2, 2019 at 15:52
Hello Ciaran, could you please describe what is the exact error you are getting? Furthermore, regarding your input data, could you describe where your file paths are stored? I haven't managed to find them. Thanks a lot. — carlesgg97
– carlesgg97, Commented Dec 2, 2019 at 16:22
@carlesgg97 The images are stored in a folder called "Signatures". The data in the spreadsheet related to the images is a file path which points to specific images in the Signatures folder. The error I am getting is on substring in motorCommEngineerSig(row, body) . I need to make a substring of the file path, starting at the very first character after / to get the file name which allows me to find the image and append it to a template. In the function motorElectInstallSignature(row, body) there are no problems. The typeError only persists in motorCommEngineerSig(row, body) . — Ciaran Crowley
– Ciaran Crowley, Commented Dec 2, 2019 at 16:31
@CiaranCrowley Hello, were you able to fix your issue with any of below answers? I am having a similar issue and would like to know about your issue's outcome... Thanks! — carlesgg97
– carlesgg97, Commented Dec 3, 2019 at 10:34

Adam Stevenson · Accepted Answer · 2019-12-02 16:42:05Z

1

I think your issue is with how you're defining signature1 in motorCommEngineerSig():

var signature1 = row[69];

row[69] refers to the 70th (0-index) column in your data. However, where you've previously defined row, you only provided 47 columns of data:

var data = sheet.getRange(2, 2, 10, 47).getValues();
  .
  .
  .
var row = data[rowNumber];

In motorElectInstallSignature(), you defined the signature in column 22, which is in range of the data and why it worked for that function.

answered Dec 2, 2019 at 16:42

Adam Stevenson

7373 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ale13 · Accepted Answer · 2019-12-02 16:58:07Z

From MDN JavaScript reference:

A TypeError may be thrown when an operand or argument passed to a function is incompatible with the type expected by that operator or function, or when attempting to modify a value that cannot be changed, or when attempting to use a value in an inappropriate way.

That happens essentially because the element you get from row, in your case row[69] does not have a substring method. That could be because:

The element exists in the row, but is not of the string type. The element could instead be a Number, Object, etc.
The element is not present in the list (the list does not hold that many items). In this case, the row[69] expression returns undefined, which does not posses that method thus returning an error when invoked.

Based on your particular error message, the second option seems to be the one occurring.

In order to fix your issue I recommend you use the built-in debugging tools such as console.log or Logger.log.

Furthermore, I would also recommend you check out the following link: Apps Script troubleshooting.

Collectives™ on Stack Overflow

"TypeError: Cannot read property 'substring' of undefined" while using two substrings in two different functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related