时间戳

首页 > 解决方案 > 多个条件语句出错；左赋值错误

google-apps-script - 多个条件语句出错；左赋值错误

问题描述

我在我的谷歌应用程序脚本中添加了一些条件语句，然后我得到一个“左分配错误”。我不确定我理解这意味着什么或错误在我的代码中的位置。

我也想知道是否有另一种方法来保存这些值，因为有时我会收到一个错误，告诉我“get.Value()”函数被使用了太多？

任何帮助，将不胜感激！谢谢

function SendReceiptSAPI19() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

//Global variables
 var tempItccaM = '1f_dujgqVfYlGFG0OGYBlzoBYpzRMXj7vTsYIVW4i4to';
 var tempItccaF = '1Hk9xgsyO-PYuS-YR0R0rEOfFtZZn3SB3yx_tipEA5ps';
 var tempUispM = '1yKCfIfDdt31rhxFpigT83wrxuPMEwsO-2Xt0dsIFHXo';
 var tempUispF = '1H6kZOT8ZH1ubU8cqHNi1nZjzbw298o1gpu3O_-prl7k'; 
 var tempAdoM = '1AD1aI6UeJJa1GF4tEcXIyx4wJjwU6F1NK54WUrTtarA';
 var tempAdoF = '1p0a2F4EZwYqZAjYk31XWEOwoYygzmOM8KxuNXCZ0ubw'; 
 var docName = 'Ricevuta 2019';

//testing how to make a for loop
  for(var i = 2; i < 3; i++) {
//getting the Name and email and storing it in a variable
  var FName = activeSheet.getRange(i,5).getValue();
  var SName = activeSheet.getRange(i, 4).getValue();
  var Email = activeSheet.getRange(i,16).getValue();
  var CF = activeSheet.getRange(i,12).getValue();
  var Residente = activeSheet.getRange(i,9).getValue();
  var Civ = activeSheet.getRange(i,10).getValue();
  var cap = activeSheet.getRange(i,11).getValue();
  var num = activeSheet.getRange(i,2).getValue();
  var anno = activeSheet.getRange(i,24).getValue();
  var itcca = activeSheet.getRange(i,21).getValue();
  var uisp = activeSheet.getRange(i,19).getValue();
  var ado = activeSheet.getRange(i,22).getValue();
  var Receipt = activeSheet.getRange(i,31).getValue();
  var Pronto = activeSheet.getRange(i,30).getValue();
  var Paid = activeSheet.getRange(i,29).getValue();
  var Sesso = activeSheet.getRange(i,6).getValue();

//make a if-else statement to check for receipt and payment
//FOR MALES: checking for teachers, even those that do not pay uisp but do pay ado
if (Paid==1 && Pronto ==1 && Receipt!=1 && itcca>=30 && ado==30 && Sesso=='M'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempAdoM)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
  copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Uisp@', uisp);
  copyBody.replaceText('@Ado@', ado);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
  var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez' +
       '</body>'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

} //FOR FEMALE TEACHERS
  else if(Paid==1 && Pronto ==1 && Receipt!=1 && itcca>=30 && ado==30 && Sesso=='F'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempAdoF)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
  copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Uisp@', uisp);
  copyBody.replaceText('@Ado@', ado);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
  var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez' +
       '</body>'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

}

//FOR MALE UISP/ITCCA STUDENTS
else if (Paid==1 && Pronto ==1 && Receipt!=1 && itcca>=30 && uisp>=10 && Sesso='M'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempUispM)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
  copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Uisp@', uisp);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
 var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

} 
//FOR FEMALE UISP/ITCCA STUDENTS
 else if (Paid==1 && Pronto ==1 && Receipt!=1 && itcca>=30 && uisp>=10 && Sesso='F'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempUispF)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
  copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Uisp@', uisp);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
 var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

}
//FOR MALE ITCCA ONLY STUDENTS
else if(Paid==1 && Pronto ==1 && Receipt!=1 && itcca==30 && Sesso='M'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempItccaM)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
 var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

}
//FOR FEMALE ITCCA ONLY STUDENTS
else if(Paid==1 && Pronto ==1 && Receipt!=1 && itcca==30 && Sesso='F'){

  //making a copy of the template document
  var copyId   = DriveApp.getFileById(tempItccaF)
  .makeCopy(docName + ' per ' + num)
  .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();
  //replace placeholders in the copy document
 copyBody.replaceText('@FName@', FName);
  copyBody.replaceText('@SName@', SName);
  copyBody.replaceText('@CF@', CF);
  copyBody.replaceText('@Residente@', Residente);
  copyBody.replaceText('@Civ@', Civ);
  copyBody.replaceText('@CAP@', cap);
  copyBody.replaceText('@N@', num);
  copyBody.replaceText('@Data@', anno);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf"); 
  //attach pdf and send email
  var subject = "Ricevuta Itcca 2018-2019";
 var html = 
      '<body>' +
        'Questa la tua ricevuta delle quote pagate. <br/>' +
          'Se vi sono errori per favore rispondi a questa email e segnalaceli, grazie. <br/>' +
            '<br/>' +
            'Cordiali saluti, <br/>' +
              'Carlo Lopez'
  MailApp.sendEmail(
    Email, 
    subject, 
    html, {
      htmlBody: html,
      attachments: pdf
    }
  );
  activeSheet.getRange(i, 31).setValue(1)

}


}

}

标签： google-apps-scriptconditionalconditional-formatting

解决方案

您的错误可能来自&& Sesso='M'（l.126）。然后几行之后，同样的&& Sesso='F'，然后一次&& Sesso='M'又一次&& Sesso='F'。您可能也应该;在分配var html....

最后，我强烈建议您使用getvalues()并setValues()限制对电子表格的访问。请记住，GS 和 sheet 之间的通信非常昂贵，并且应该最大程度地限制以避免性能问题。这并不复杂：你只读取一次数据并获得一个大数组来解析 GS 端，然后生成一个新的大数组来一次性保存所有数据。

推荐阅读