I have an onFormSubmit function that triggers onFormSubmit and gets some additional information from external sources for the submitted entry. It seems to be fine before line 113. Logger output before the line are correct, but that line of code does not write to the sheet. and line 114 gives error msg "Exception: Enter a number". I cannot figure out what the problem is. Any insights are much appreciated. Here is the code:(sheet id's are deleted)
var moment = Moment.load();
function onFormSubmit(e) {
var eventObject = getFormResponse();
var sheetr = SpreadsheetApp.openById("****").getSheetByName("Form Responses 1");
var sheet = SpreadsheetApp.openById("****").getSheetByName("Sheet1");
var orders = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
var sheettlrcor=SpreadsheetApp.openById("****").getSheetByName("Sheet1");//
var sheettl2wor=SpreadsheetApp.openById("****").getSheetByName("Sheet1");//
var tlrcor=sheettlrcor.getRange(1,1,sheettlrcor.getLastRow(),sheettlrcor.getLastColumn()).getValues();//
var tl2wor=sheettl2wor.getRange(1,1,sheettl2wor.getLastRow(),sheettl2wor.getLastColumn()).getValues();
for (var k = orders.length-1; k >0; k--) {
if (orders[k][0] != "" && e.source.getActiveSheet().getRange(e.range.rowStart, 2).getValue().toString().toUpperCase()==(orders[k][1].toString().toUpperCase()) ){
e.source.getActiveSheet().getRange(e.range.rowStart,16).setValue(orders[k][10]);
var value = SpreadsheetApp.newRichTextValue()
.setText("Create Return Label")
.setLinkUrl("https://apps.goshippo.com/orders?q="+orders[k][1])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,14).setRichTextValue(value);
var value1 = SpreadsheetApp.newRichTextValue()
.setText(orders[k][1])
.setLinkUrl(orders[k][45])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,13).setRichTextValue(value1);
break;
}
}
if(k==0)
for (var k = orders.length-1; k >0; k--) {
if (orders[k][0] != "" && e.source.getActiveSheet().getRange(e.range.rowStart, 3).getValue().toString().toUpperCase()==orders[k][10].toString().toUpperCase()&& e.source.getActiveSheet().getRange(e.range.rowStart, 2).getValue().toString().toUpperCase().includes(orders[k][1].toString().toUpperCase()) ){
e.source.getActiveSheet().getRange(e.range.rowStart,16).setValue(orders[k][10]);
var value = SpreadsheetApp.newRichTextValue()
.setText("Create Return Label")
.setLinkUrl("https://apps.goshippo.com/orders?q="+orders[k][1])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,14).setRichTextValue(value);
var value1 = SpreadsheetApp.newRichTextValue()
.setText(orders[k][1])
.setLinkUrl(orders[k][45])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,13).setRichTextValue(value1);
break;
}
}
if(k==0)
for (var k = orders.length-1; k >0; k--) {
if(orders[k][10]+""!=""&& e.source.getActiveSheet().getRange(e.range.rowStart, 3).getValue().toString().toUpperCase()==orders[k][10].toString().toUpperCase()){
e.source.getActiveSheet().getRange(e.range.rowStart,16).setValue(orders[k][10]);
var value = SpreadsheetApp.newRichTextValue()
.setText("Create Return Label")
.setLinkUrl("https://apps.goshippo.com/orders?q="+orders[k][1])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,14).setRichTextValue(value);
var value1 = SpreadsheetApp.newRichTextValue()
.setText(orders[k][1])
.setLinkUrl(orders[k][45])
.build();
e.source.getActiveSheet().getRange(e.range.rowStart,13).setRichTextValue(value1);
break;
}
}
var emailAddress1 = e.source.getActiveSheet().getRange(e.range.rowStart,3).getValue();
var subject1 = '*****';
var message1 = '*****'; // Second column
try{
var emailAddress1 = e.source.getActiveSheet().getRange(e.range.rowStart,3).getValue();
if(emailAddress1+""!="")
MailApp.sendEmail(emailAddress1, subject1, message1, {
cc: e.source.getActiveSheet().getRange(e.range.rowStart,16).getValue(),
replyTo: '***',
name: '***'
});
}catch(error){
var emailAddress1 = e.source.getActiveSheet().getRange(e.range.rowStart,16).getValue();
if(emailAddress1+""!="")
try{
MailApp.sendEmail(emailAddress1, subject1, message1, {
cc: e.source.getActiveSheet().getRange(e.range.rowStart,3).getValue(),
replyTo: '****',
name: '****'
});
}catch(err){
MailApp.sendEmail(emailAddress1, subject1, message1, {
replyTo: '*****',
name: '****'
});
}
}
e.source.getActiveSheet().getRange(e.range.rowStart,20).setBorder(null, true, null, null, null, null);
e.source.getActiveSheet().getRange(e.range.rowStart,23).setBorder(null, null, null, true, null, null);
for(var q=e.source.getActiveSheet().getLastRow();q>0;q--){
if((e.source.getActiveSheet().getRange(q,2).getValue()+"").includes(e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue()+"")&&e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue()+""!=""){
break;
}
}
var found3=false;
Logger.log((e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue()+"").toString().toUpperCase());
for(var m=tlrcor.length-1;m>0;m--){
if((tlrcor[m][1]+"").toString().toUpperCase().includes((e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue()+"").toString().toUpperCase())&&tlrcor[m][33]+""!=""){
Logger.log(tlrcor[m][33]);
found3=true;
try{var trinfo=_17trackinfo(tlrcor[m][33]+"");}catch(er){Logger.log(er);var trinfo=[["",""]];}
Logger.log(trinfo+" a");
const value2 = SpreadsheetApp.newRichTextValue()
.setText(tlrcor[m][33]+"")
.setLinkUrl("https://t.17track.net/en#nums="+tlrcor[m][33]+"")
.build();
Logger.log(q);
Logger.log("a1");
Logger.log(value2);
try{sheetr.getRange(q,35).setRichTextValue(value2);}catch(el){Logger.log(el);sheetr.getRange(e.range.rowStart,35).setValue(tlrcor[m][33]+"");}
Logger.log(sheetr.getRange(1551,2).getValue());
if((trinfo[0][0]+"").toString().toUpperCase().includes("DELIVERED")){
sheetr.getRange(q,36).setValue(trinfo[0][1]);
Logger.log(e.source.getActiveSheet().getRange(e.range.rowStart,36).getValue());
sheetr.getRange(q,37).setValue(((moment(trinfo[0][1]).toDate()-moment(sheetr.getRange(q,1).getValue()).toDate())/1000/60/60/24).toFixed(2)+" days");
}
break;
}
}
if(found3==false){
var found4=false;
for(var p=tl2wor.length-1;p>0;p--){
if((tl2wor[p][1]+"").toString().toUpperCase().includes((e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue()+"").toString().toUpperCase())&&tl2wor[p][33]+""!=""){
Logger.log(tl2wor[p][33]);
found4=true;
try{ var trinfo=_17trackinfo(tl2wor[p][33]+"");}catch(e1){Logger.log(e1);var trinfo=[["",""]];}
Logger.log(trinfo+" b");
const value3 = SpreadsheetApp.newRichTextValue()
.setText(tl2wor[p][33]+"")
.setLinkUrl("https://t.17track.net/en#nums="+tl2wor[p][33]+"")
.build();
sheetr.getRange(q,35).setRichTextValue(value3);
if((trinfo[0][0]+"").toString().toUpperCase().includes("DELIVERED")){
sheetr.getRange(q,36).setValue(trinfo[0][1]);
sheetr.getRange(q,37).setValue(((moment(trinfo[0][1]).toDate()-moment(e.source.getActiveSheet().getRange(q,1).getValue()).toDate())/1000/60/60/24).toFixed(2)+" days");
}
break;
}
}
}
}
Here is output of log:
Stackdriver logs
Sep 16, 2020, 11:02:08 PM Info PA23453
Sep 16, 2020, 11:03:25 PM Info 3.9538180997E11
Sep 16, 2020, 11:03:27 PM Info Delivered,2020-08-11 13:40,SPARTANBURG, SC,Delivered,,, a
Sep 16, 2020, 11:03:27 PM Info 1567.0
Sep 16, 2020, 11:03:27 PM Info a1
Sep 16, 2020, 11:03:27 PM Info com.google.apps.maestro.server.beans.trix.impl.RichTextValueApiAdapter@d5de711
Sep 16, 2020, 11:03:27 PM Error Exception: Enter a number
at onFormSubmit(Code:114:48)