Google script cannot write/read to Google sheet and give error "Exception: Enter a number" for getValue()

118 Views Asked by At

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

There are 0 best solutions below