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);
}
}
}
here is information of the sheet

