脚本在onFormSubmit上运行多次 [英] Script running multiple times onFormSubmit

问题描述

我将以下代码设置为基于onFormSubmit触发器运行，但有时在同一提交下将运行多次.我想验证它是否已经复制了该行，如果要复制，则停止脚本.

I have the following code set to run based on a onFormSubmit trigger but it will sometimes run multiple times with the same submission. I want to verify if it already copied the row and if so to stop the script.

function toDo(){
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var jobs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jobs");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, 19).getValues();

  jobs.appendRow(row[0]);
  //copyValuesOnly(copyFromRange, copyToRangeStart);

  var si = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobs');

  var range = si.getRange("A2:R");

  range.sort({column: 5,ascending: true}),({column: 1, ascending:true});

}

推荐答案

这是GAS +表单的已知问题.解决该问题的方法是创建一个脚本锁，该锁在一段时间内拒绝所有其他尝试(使它们尽早返回).

this is a known problem with GAS + Forms. The way that you solve it is by creating a script lock that rejects (causing them to return early) all other attempts within a period of time.

function toDo(){
     SpreadsheetApp.flush();
     var lock = LockService.getScriptLock();
  try {
    lock.waitLock(5000); 
     } catch (e) {
        Logger.log('Could not obtain lock after 5seconds.');
        return HtmlService.createHtmlOutput("<b> Server Busy please try after some time <p>")
        // In case this a server side code called asynchronously you return a error code and display the appropriate message on the client side
        return "Error: Server busy try again later... Sorry :("
     }
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var jobs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jobs");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, 19).getValues();

  jobs.appendRow(row[0]);
  //copyValuesOnly(copyFromRange, copyToRangeStart);

  var si = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobs');

  var range = si.getRange("A2:R");

  range.sort({column: 5,ascending: true}),({column: 1, ascending:true});
  Utilities.sleep(5000);
  lock.releaseLock)();
}

我有脚本执行此操作最多8次，通常每2-3秒执行一次.使用此解决方案，您可以在开始时进行锁定，然后在结束时进行睡眠，以确保处理时间大于等待时间. (在这里我用了5秒钟，应该可以防止重复输入).

I've had scripts that do this up to 8 times, and usually do it every 2-3 seconds. With this solution you are making a lock at the beginning and then sleeping at the end to make sure that the process time is greater than the wait time. (Here I used 5 seconds, that should prevent the double entry).

这篇关于脚本在onFormSubmit上运行多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！