Google Sheet Script runs 2 times

Question

I have the problem that I often get duplicate entries in my calendar.

Based on my current situation:

Data is entered into a web form.
These are then entered in my SQL database.
Then sent via script to a Google Spreadsheet. So far everything is OK.
Another Google Sheet in a new folder receives the data received.
Using a trigger on change, this now sends the data to the calendar via script. The problem, however, is this often happens twice for no apparent reason.

Before I chose the solution with the further table, as described in point 4, there were even several entries.

The code for the form entry in Google Sheets is from jamiewilson-form-to-Google-Sheets and the code for the entry in the calendar from the page toptorials.

function Kalendereintrag_func() {
  var kalenderID = 'xxxxxxxx@group.calendar.google.com';
  var meinKalender = CalendarApp.getCalendarById(kalenderID);
  var ss = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxx').getSheetByName(
    'Datenimport'
  );
  var TabelleVonTag = ss.getRange('A2').getValue();
  var TabelleVonZeit = ss.getRange('B2').getValue();
  var TabelleBisTag = ss.getRange('C2').getValue();
  var TabelleBisZeit = ss.getRange('D2').getValue();
  var KalenderVon = new Date(TabelleVonTag);
  KalenderVon.setHours(TabelleVonZeit.getHours());
  KalenderVon.setMinutes(TabelleVonZeit.getMinutes());
  var KalenderBis = new Date(TabelleBisTag);
  KalenderBis.setHours(TabelleBisZeit.getHours());
  KalenderBis.setMinutes(TabelleBisZeit.getMinutes());
  var Titel = ss.getRange('E2').getValue();
  var Beschreibung = ss.getRange('F2').getValue();
  var Ort = ss.getRange('G2').getValue();
  var termin = meinKalender.createEvent(Titel, KalenderVon, KalenderBis, {
    description: Beschreibung,
    location: Ort,
  });
}

function onFormSubmit(e) {
  var targetCell = e.range.offset(0, e.range.getNumColumns(), 1, 1);
  var lock = LockService.getScriptLock();
  lock.waitLock(30000);
  var ticketNumber =
    Number(ScriptProperties.getProperty('lastTicketNumber')) + 1;
  ScriptProperties.setProperty('lastTicketNumber', ticketNumber);
  lock.releaseLock();
  targetCell.setValue(ticketNumber);
}

I have a Web form. I give the user inputs in this form with a script to a Google Sheet named: "Dateneingang". That's run fine. In a new sheet of this Google table named:"Auswertung" I get the last entry of this sheet. It runs fine and I have no double entries. But in past, when I try to send from here data's to my Google calendar, I have problems to trigger it. So I take my solution way to get this data's in a new table per "Importrange".

There I give trigger by change. And here is my problem. It sends data 2 times. But not every time but very often.

I think OK I delete after a few seconds the entries but there is a new change what activate the trigger. Then I have work with a solution about.

But why sometimes run correctly and other time not.

thanks, maybe i have found a solution: i have changed the lockservice code. — Alex, Aug 17 '20 at 00:27
Alex, btw, if you found a solution, you can post as a self answer — Oleg Valter is with Ukraine, Aug 17 '20 at 00:29

score 2 · Answer 1 · edited Oct 15 '20 at 15:17

var lock = LockService.getScriptLock();
        lock.waitLock(30000);
        var ticketNumber = Number(ScriptProperties.getProperty('lastTicketNumber')) + 1;
        ScriptProperties.setProperty('lastTicketNumber', ticketNumber);
        lock.releaseLock();
        targetCell.setValue(ticketNumber);
        }

to

 function myFunction() {
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(5000); // wait 5 seconds try to get lock
  } catch (e) {
    Logger.log('Could not obtain lock after 5 seconds.');
  }
  Utilities.sleep(10000);
  lock.releaseLock();}

Found here: https://stackoverflow.com/a/63182230/10028103

Google Sheet Script runs 2 times

1 Answers1