TypeError: cannot read properties '15' of undefined

Viewed 50

I write 4 for loop that use same variable values,but only this one shows this error message. I don't understand what's wrong with it.

This error showups when it run to if(values[rowCount][15] == 1 && values[rowCount][17]=='工程師已完工') this line.

My sheet has 13 row, 20 col and 15th col in each row is a checkbox.

Does anyone have met this problem before?

var spreadsheet = SpreadsheetApp.openById('10DY3Qlw6EwC_NnxSxoU-0H88PYKv83rOaRImn4yubDc');
  var sheet = spreadsheet.getSheetByName('OOO');
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var range = sheet.getRange(1,1,lastRow,lastCol);
  var values = range.getValues();
for(rowCount = 1; rowCount <= lastRow + 1; rowCount++){
    if(values [rowCount][16] == 1 && values [rowCount][15] == 1 && values[rowCount][17] != '已發信通知'){
      break;
    }
    if(values[rowCount][15] == 1 && values[rowCount][17] == '客戶已確認'){
      continue;
    }
    if(values[rowCount][15] == 1 && values[rowCount][17] == '已發信通知' && values [rowCount][16] == 0){
      var img = values[rowCount][18].split(",")
      //console.log(img);
      if(img[0] == "data:image/png;base64"){
        sheet.getRange(rowCount+1, colCount + 1).setValue('客戶已確認');
        
        var datauri = values[rowCount][18].split("data:image/png;base64,");
        datauri = Utilities.base64Decode(datauri[1]); //use datauri[1] because [0] is a blank string
        datauri = Utilities.newBlob(datauri, 'image/png', values[rowCount][0]+'.png'); //define the name and mymetype when creating the blob

        var image_folder = DriveApp.getFolderById("1RCqldGIx10Vmp4aM-ZO8vz3SAx6Pi8NL");

        var image_1 = image_folder.createFile(datauri);

        var documen = DriveApp.getFolderById("1ehyZY0h9l0m9k1z6ZLq8vDsGZ1OezzzL").getFiles();
        let file_ID;
        while (documen.hasNext()) {
          let file = documen.next();
          if(file.getName() == values[rowCount][0]){
            file_ID = file.getId();
            break;
          }
        };

        var documentId = file_ID //要插入的文件的id
        var replaceText = "Customer_Sign";
        var imageFileId = image_1.getId();  //要插入的圖片的ID
        var body = DocumentApp.openById(documentId).getBody();
        var image = DriveApp.getFileById(imageFileId).getBlob();
        var insert_basictime =  new Date();
  
        var insert_time = insert_basictime.getFullYear() + "/" + (1+insert_basictime.getMonth()) + "/" + insert_basictime.getDate();

        replaceTextToImage(body, replaceText, image, 150);
        body.replaceText("{{Accept_Date}}", insert_time);


      }
    }
  }
//console.log(values[2]);
//確認完工後的狀態更新並將完工簽名貼上  
  for(rowCount = 1; rowCount <= lastRow + 1; rowCount++){
    if(rowCount >= lastRow && rowCount > 2){
      break;
    }
    var closed_check = 0; 
    var eng_signID = ' '; 
    if(values[rowCount][15] == 1 && values [rowCount][17] == '客戶已確認'){
      closed_check = 1;
    }
    if(values[rowCount][17] == '工程師已完工'){
      sent_Mail = 2;
      continue;
    }
    if(values[rowCount][16] == 1 && closed_check == 1){
      if(sent_Mail != 2){
        sheet.getRange(rowCount+1, colCount + 1).setValue('工程師已完工');
        var check = 0;
        for(check = 1;check < lastRow + 1; check++){
          if(eng_values[check][0] == values[rowCount][3]){
            eng_signID = eng_values[check][1];
            break;
          }
        }
      var searchRight = DriveApp.getFolderById("1ehyZY0h9l0m9k1z6ZLq8vDsGZ1OezzzL").getFiles();
      var engClosed_docID;

      while (searchRight.hasNext()) {
          let check_file = searchRight.next();
          if(check_file.getName() == values[rowCount][0]){
            engClosed_docID = check_file.getId();
            break;
          }
      };

      var engClosed_body = DocumentApp.openById(engClosed_docID).getBody();
      var replaceText = "Eng_Sign2";
      var engSign_image = DriveApp.getFileById(eng_signID).getBlob();
      var engClosed_time = new Date();

      engClosed_time = engClosed_time.getFullYear() + "/" + (1+engClosed_time.getMonth()) + "/" + engClosed_time.getDate();

      replaceTextToImage(engClosed_body, replaceText, engSign_image, 150);
      engClosed_body.replaceText("{{Close_Date}}", engClosed_time);


      }
    }
   
  }

enter image description here

enter image description here

here is information of the sheet

0 Answers
Related