CODEWITHSUNDEEP
google-apps-script
user2356849
user2356849

Reputation: 1

Google apps script: reliability issue.. setValue onformsubmit less than consistent

Hopefully this is a reasonably straight forward question. Firstly, some context information: I collect information from a form into a google spreadsheet to record entries to cycle races. I use the onformsubmit trigger to run some code to do the following; 1) I check how many entries I have by rider Grade and compare these to some pre-set limits 2) Based on the pre-set limits, I work out whether the rider's entry is "provisionally entered" or "waitlisted" (I call this EntryType eg EntryType = 'waitlisted') 3) I populate some variables so as to send a confirmation email, quoting back entry details submitted by the user and advise them whether their entry is waitlisted or not. 4) I write the EntryType alongside the form submitted data in the spreadsheet so I have a record of what EntryType each rider was advised by email.

The code works fine apart from one little issue with step 4 above. Most of the time this works fine but if entries come in close together - eg a couple of seconds apart - step 4 may be left blank. The confirmation email will send, the form data will write to the spreadsheet, just that the "EntryType" will not be written to the spreadsheet alongside the form data.

I suspect that the data from the form for the next record coming in takes precedence and the write function fails without erroring? Just a guess.

Could someone offer some suggestions? I believe this code is very close, unfortunately not bullet proof as yet.

Regards, Colin

function onFormSubmit(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var oGradeSubmitted = e.values[13];
var oGradeLevelSubmitted =   e.values[14];

// Get the Type of Entry Limits to Apply (Handicap, Scratch or None)  
var oRangeLT  = ss.getRangeByName("oLimitType");
var oLimitType = oRangeLT.getValue();


//== start ===G R A D E    L I M I T =================================================================================    



  if (oLimitType == 'Scratch') { 

   // Concatenate Generic Range Name & Grade Submitted to get Specific Ranges Names   
  var oLimitVar = "oLimit" + oGradeSubmitted;  //These are now the LOWER waitlist limits
  var oLimitUpper = "oLimitUpper" + oGradeSubmitted;  //These are the UPPER waitlist limits
  var oCountVar = "oCount" + oGradeSubmitted; //The count is based on grade submitted, not handicapping grade so as to not upset waitlist order

  // Get Data from Specific Ranges
  // 1) Grade Limit Data

    //lower waitlist limits
    var oRangeLV = ss.getRangeByName(oLimitVar);
    var oLimitData = oRangeLV.getValues();

    //upper waitlist limits
    var oRangeLVU = ss.getRangeByName(oLimitUpper);
    var oLimitDataUpper = oRangeLVU.getValues();


  // 2) Grade Count Data
  var oRangeCV = ss.getRangeByName(oCountVar);
  var oCountData = oRangeCV.getValues();  



  // Write some Data into the same row as the current form submission data  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses");
  var row =  sheet.getLastRow();


  if (+oCountData >= +oLimitData && +oCountData <= +oLimitDataUpper) {
    sheet.getRange(row,18).setValue("Waitlisted");
    var oEntryStatus = "Waitlisted"; 
  } else {};

      if (+oCountData > +oLimitDataUpper) {
    sheet.getRange(row,18).setValue("Waitlisted but doubtful");
    var oEntryStatus = "Waitlisted but doubtful";  
    } else {};

    }
    }

Upvotes: 0

Views: 736

Answers (1)

AdamL
AdamL

Reputation: 24609

To get around the concurrency issue you could use the e.range parameter that is passed rather than getLastRow():

var row = e.range.getRow();

https://developers.google.com/apps-script/understanding_events (scroll to bottom)

Upvotes: 1

Related Questions