检查最后一行状态是否 = X

有人能帮我弄清楚为什么这不起作用吗？我有一个类似的脚本(正在工作(，它发送电子邮件，然后更新特定列中的最新行以阻止它再次发送电子邮件。我采用了那段脚本并将其实现到下面的脚本中。但是，它似乎不起作用...

脚本的核心工作(当用户订阅表单时，它会更新工作表并基于模板创建一个新工作表，并以他们的名字作为工作表名称，然后执行导入范围以将他们的数据引入其中(通过表单检索的 URL(。

我需要它来处理最新行，以便它为每个新表单提交创建一个。我从中获取的脚本工作正常，包含在底部。

非工作脚本

// This constant is written in column E for rows for which importrange
// has been completed.
var IMPORTED = "IMPORTED"; 
function onSubmit(e){
  var spreadsheet = SpreadsheetApp.openById("1UDAb8ATDChnVbi_2jktsoAfumrq0Tj8gO8xotYgb6JA") // <- Taken from other script
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); // <- Taken from other script
  var ss = e.source;
  var Name = e.values[2];
  var templateSheet = ss.getSheetByName('Template');
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var col = sh.getActiveRange().getColumnIndex();
  var activeRange = sh.getActiveRange().getA1Notation();
  var startRow = 1;  // First row of data to process
  var data = sheet.getDataRange().getValues();
   for(var i=startRow; i<data.length; i++) { // <- Taken from other script
    var row = data[i]; // <- Taken from other script
    var Training_URL = row[3];       // <- Taken from other script
  var newSheet = ss.insertSheet(1, {template: templateSheet});
  var imported = row[4] // <- Taken from other script
  if (imported != IMPORTED) { // <- Taken from other script
    newSheet.setName(Name);
    newSheet.showSheet();
    newSheet.setValue('=importrange("' + Training_URL + ',"Training!A1:I24"');
    sheet.getRange(startRow + i, 5).setvalue(IMPORTED); // <- Taken from other script
  ss.getRange 
  SpreadsheetApp.flush(); // <- Taken from other script
    }
  }
}

原始脚本

var EMAIL_SENT = "EMAIL_SENT";
function sendEmails2() {
  var spreadsheet = SpreadsheetApp.openById("1wHojHaB8Q3g06Hc_FnsqzRTY7KAma1QEbkb4InPF2wQ");
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var startRow = 1;  // First row of data to process
  var data = sheet.getDataRange().getValues();
  for(var i=startRow; i<data.length; i++) {
    var row = data[i];
    var emailAddress = row[1];
    var SNOW_URL = row[4];
    var message = "<HTML><BODY>"
    + "</HTML></BODY>";      
    var emailSent = row[5];
    if (emailSent != EMAIL_SENT) {  // This part prevents sending duplicates
      var subject = "Your ServiceNow edit request";
      MailApp.sendEmail(emailAddress, subject, "", {htmlBody: message});
      sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();    
    }
  }
}

最后我自己设法到达那里。花了一个下午重新阅读 appscript 后，运行起来更整洁、更安全。谢谢大家的帮助。

function onSubmit(form) {
  console.log({message: 'Form submited', form: form});
  var masterSS = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = masterSS.getSheetByName('Template');
  var responseSheet = masterSS.getSheetByName('Responses');
  var range = form.range;
  console.log(range.getRow());
  var newSheet = masterSS.insertSheet(1, {template: templateSheet});
  var userName = form.namedValues['Email address'][0].split('@')[0];
  newSheet.setName(userName.split('.')[0] + ' ' + userName.split('.')[1]);
  newSheet.getRange(1, 1).setValue('=importrange("' + form.namedValues['URL of your sheet'][0] + '" ,"Training!A1:I24")');
  responseSheet.getRange(range.getRow(), 5).setValue('IMPORTED');
  console.log('***** All done ******');
}

相关内容

最新更新

热门标签：