Does anyone know the limit of post requests for a google spreadsheet?

Question

I used the following code to use a google sheet as an endpoint for a webhook. The problem is that I am not receiving all the events from the webhook, apparently there are to many events at the same time and the spreadsheet is not able catch them all. Here is the code I used:

function doPost(e){
    if (e==undefined){
        Logger.log("No data");
        return HtmlService.createHtmlOutput("Need data");
    }
    var mydata=JSON.parse(e.postData.contents);
    var i;
    for (i=0;i<mydata.length; i++){
        var email=mydata[i].email;
        var event=mydata[i].event;
        var marketing_campaign_name=mydata[i].marketing_campaign_name;
        var url=mydata[i].url;
        var timestamp=mydata[i].timestamp;
        var fecha=new Date(timestamp*1000);
        var ip=mydata[i].ip;
        var sheet=SpreadsheetApp.getActiveSheet();
        var lastRow=Math.max(sheet.getLastRow(),1);
        sheet.insertRowAfter(lastRow);
        sheet.getRange(lastRow + 1, 1).setValue(email);
        sheet.getRange(lastRow + 1, 2).setValue(event);
        sheet.getRange(lastRow + 1, 3).setValue(url);
        sheet.getRange(lastRow + 1, 4).setValue(marketing_campaign_name);
        sheet.getRange(lastRow + 1, 5).setValue(fecha);
        sheet.getRange(lastRow + 1, 6).setValue(ip);
    }
    SpreadsheetApp.flush();
    return HtmlService.createHtmlOutput("post request received");
}

Answer 1

There is a limit of 30 simultaneous executions, which is listed on the quotas page.

However, this limit might not be your problem, you are probably also dealing with a race condition problem. Currently, if you receive multiple doPost requests close to simultaneously, they will be reading and writing to the spreadsheet at the same time. This means multiple requests could get the same lastRow value, and then write all their results to the same row.

The best solution in your case is to use appendRow(), which specifically avoids this problem.

This operation is atomic; it prevents issues where a user asks for the last row, and then writes to that row, and an intervening mutation occurs between getting the last row and writing to it.

To use appendrow, build your entire row in an array, then append that array to the sheet.

 var sheet=SpreadsheetApp.getActiveSheet();
 var row = [email,event,url,marketing_campaign_name,fecha,ip];
 sheet.appendRow(row);

An alternative solution would be to use a Script Lock, which lets you "lock" a section of the code so that it can't be executed simultaneously. This would make more sense if you were doing more complex actions than writing to a sheet, and needed to be able to ensure those actions were never run simultaneously.