How to improve Google Apps Script bulk invitation efficiency? Too many API calls may be resulting in error: GoogleJsonResponseException

Question

Explanation:

I have a Google Apps Script project that automatically scans a bunch of rows and invites the email addresses contained in each row. It knows which set of invites to send the email addresses to row by row depending on the label in each row. Essentially the row will be invited to a specific set of invites. See example below:

type/label	status	email address 1	email address 2
apple 1	...	1@external	1@business
apple 1	...	2@external	2@business
apple 1	...	3@external	3@business
apple 1	...	4@external	4@business
apple 2	...	5@external	5@business
apple 2	...	6@external	6@business

The type/label in the first column ("apple 1" and "apple 2" for example) is matched in another sheet called Settings where a range of invites are listed below each label. If in the main sheet a row says "apple 1" - then it goes to Settings, finds "apple 1" and gets range below the label, gets event ID from each link listed below it, and invites the row's email addresses to each invite. This results in a lot of back and forth inviting, row by row.

Example Settings sheet:

apple 1	apple 2	apple 3
google calendar invite link 1	google calendar invite link 2
google calendar invite link 3	google calendar invite link 4

As I understand it, the script currently scans row #1, gets the email addresses in columns 3 and 4, gets the label for that row ("apple 1"), matches the label ("apple 1") in the settings, gets the invites listed below "apple 1" in the settings, gets their event IDs, and invites the email addresses for that row to those set of invites. If the invites were successful for that row, it writes "invited" in the status column and continues on with the next rows.

As you can see this is really inefficient. It works seamlessly (though slowly, taking about 5-8 minutes) for 100 rows of email addresses where the labels are pointing to 3-5 unique invite links each label. So if I assigned "apple 1" and "apple 2" to 50 rows each, and in the settings "apple 1" and "apple 2" had 4 unique invites listed below each of them, it invites perfectly fine, but very slowly.

Solutions:

I want for the script to run more efficiently. Perhaps it should organize "apple 1" rows and "apple 2" and store their respective email addresses in separate arrays, then invite them all in one go to each invite?

Hypothetical flow:

• 50 rows with "apple 1"
• "apple 1" in settings has 10 unique invites linked below it
• run program
• gather all email addresses for rows labeled "apple 1"
• list e.g. [1@external.com 1@business.com 2@external.com 2@business.com 3@external.com 3@business.com 4@external.com 4@business.com]
• go through all "apple 1" invites and invite that list in one action (is that possible?) to each invite; the same way you would edit an invite manually and paste that list then save the event
• continue doing this for invites 2-10
• mark "invited!" in the status column of each row that was successfully invited
• do the same with "apple 2"

Is this the best way / the solution and how could I rewrite the current set up to be as efficient as possible?

And/or perhaps we can use timers and an empty cell/variable to store values so that if execution is terminated, it runs it again, continuing with the stored data?

Errors:

I test on a business Gmail account, meaning execution time is 30 minutes long and the amount of invites I can send out is 10,000 (or potentially more).

First, I do not send out more than 200+ invites when the script runs. Second, half those emails being invited are primary domain (within the business) so half don't even count in the "10,000 invites" limitation which are specific to external domains per the document linked HERE.

I am not exceeding these limits, so I believe the amount of calls to the API is what's causing issues. The errors I receive are different on a personal account and business Google Suite account:

Personal:

Error: GoogleJsonResponseException: API call to calendar.events.patch failed with error: Calendar usage limits exceeded.

Business:

GoogleJsonResponseException: API call to calendar.events.patch failed with error: The service is currently unavailable.

Each pull/call may be overloading.

I will include the main script and the html for the sidebar (open from menu):

code.gs

function onInstall(e) {
    onOpen(e);
}

function onOpen() {
    var ui = SpreadsheetApp.getUi();

    ui.createMenu('► Invite Automation ◄')
        .addItem('Open Auto-Invite Program', 'openSidebar')
        .addToUi();
}

function openSidebar() {
    var html = doGet();
    SpreadsheetApp.getUi()
        .showSidebar(html);
}

function doGet(request) {
    return HtmlService.createTemplateFromFile('Campaign') // campaign.html
        .evaluate()
        .setSandboxMode(HtmlService.SandboxMode.IFRAME)
        .setTitle('Auto-Invite Program') // Header of the side bar
        .setWidth(300);
}

function include(filename) {
    return HtmlService.createHtmlOutputFromFile(filename)
        .getContent();
}


function getSettingsSheet() {
    const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (let i = 0; i < sheets.length; i++) {
        if (~sheets[i].getName().indexOf('Settings')) {
            return sheets[i];
        }
    }
}

function getEventIdFromLink(link) {
    const m = link.match(/eventedit\/(\w+)/);
    const sp = Utilities.newBlob(Utilities.base64Decode(m[1])).getDataAsString().split(" ");
    console.log(sp);
    return sp[0];
}

function getListColumn(list) {
    const settingsSheet = getSettingsSheet();
    const lastCol = settingsSheet.getLastColumn();
    const labels = settingsSheet.getRange(1, 1, 1, lastCol).getValues()[0]; // LABELS STARTING ROW IN THE SETTINGS -- labels must be written row one, everything below it is invite links
    console.log(labels);
    return (labels.indexOf(list)) + 1;
}

function getListEventIds(list) {
    const columnNum = getListColumn(list);
    const settingsSheet = getSettingsSheet();
    const events = settingsSheet.getRange(2, columnNum, settingsSheet.getLastRow()).getValues(); // EVENT STARTING ROW IN THE SETTINGS -- these are invite links that start after the labels/header in the settings
    eventIds = events.map((r) => r[0]).map((e) => e.trim()).filter((e) => e != '').map(getEventIdFromLink);
    return eventIds;
}

function getEvent(eventId) {
    // return CalendarApp.getDefaultCalendar().getEventById(eventId);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // var calendarId = ss.getSheetByName('Settings').getRange(2, 10).getValue(); // CALENDAR ID 1 OF 2
    var calendarId = CalendarApp.getDefaultCalendar().getId(); // CALENDAR ID 1 OF 2 -- i usually point to a specific calendar id, but for public display I will revert to default id

    console.log(calendarId);
    return CalendarApp.getCalendarById(calendarId).getEventById(eventId);
}

function getEmails(emailcolumn) {
    var leadSheet = SpreadsheetApp.getActiveSheet();
    return leadSheet.getRange(2, emailcolumn, leadSheet.getLastRow(), 2)
        .getValues()
        .filter(r => r[1] != 'Invited')
        .map((r) => r[0])
        .filter((e) => ~e.indexOf('@'));
}

function existsInAll(arr, search) {
    for (var i = 0; i < arr.length; i++) {
        if (!~arr[i].indexOf(search)) {
            return false
        }
    }
    return true;
}

function inviteGuests(statuscolumn, emailcolumns) {

    emailcolumns = emailcolumns.split(',').map(col => parseInt(col));

    var leadSheet = SpreadsheetApp.getActiveSheet();
    const lastRow = leadSheet.getLastRow();
    const statusValues = leadSheet.getRange(1, statuscolumn, leadSheet.getLastRow(), 1).getValues();
    // const inviteValues = leadSheet.getRange(1, 4, leadSheet.getLastRow(), 1).getValues();

    var lock = LockService.getScriptLock();

    if (lock.tryLock(1000)) { // Wait for 1s

        for (var row = 2; row <= lastRow; row++) {

            let list = statusValues[row - 1][0];
            // let inviteList = inviteValues[row - 1][0];

            if (~list.indexOf('Invited')) continue;
            // if (~inviteList.indexOf('Invited')) continue;

            list = list.replace(/\s*\(.*\)\s*/, ''); //remove parentheses

            if (list == '') continue;
            console.log(list);

            const listCol = getListColumn(list);

            if (listCol == 0) continue;

            const statusRange = leadSheet.getRange(row, statuscolumn);
            const inviteRange = leadSheet.getRange(row, 2); // COLUMN 2 IS WHERE IT NOTES "INVITED" OR "FAILED" FOR EACH RESPECTIVE ROW
            const eventIds = getListEventIds(list);


            if (eventIds.length == 0) {
                SpreadsheetApp.getActiveSpreadsheet().toast('A row failed because the label/assignment exists but had no invites linked below it in Settings.', 'Failed!', 30);
                // statusRange.setValue(`${list}`);
                inviteRange.setValue(`❌  Failed`);
                continue;
            }

            const events = eventIds.map(getEvent);

            let emails = emailcolumns.map(col => leadSheet.getRange(row, col).getValue().trim()).filter(e => e != '');

            // if (emails.length == 0) continue;

            if (emails.length == 0) {
                SpreadsheetApp.getActiveSpreadsheet().toast("A row failed because no email address was listed.", "Failed!", 30);
                inviteRange.setValue(`❌  Failed`);
                continue;
            }

            // Logger.log(emails);

            let allAttendees = []

            events.map((event) => {

                const eventId = event.getId().split('@')[0];
                // Logger.log(eventId);

                // const calendarId = CalendarApp.getDefaultCalendar().getId();
                // const calendarId = leadSheet.getSheetByName('Settings').getRange(1, 2).getValue();
                // Logger.log(calendarId);

                var ss = SpreadsheetApp.getActiveSpreadsheet();
                // var calendarId = ss.getSheetByName('Settings').getRange(2, 10).getValue(); // CALENDAR ID 2 OF 2
                var calendarId = CalendarApp.getDefaultCalendar().getId(); // CALENDAR ID 1 OF 2 -- i usually point to a specific calendar id, but for public display I will revert to default id
                const resource = Calendar.Events.get(calendarId, eventId);
                const newattendees = emails.map((e) => ({
                    email: e
                }));

                if (resource.attendees) {
                    resource.attendees.push(...newattendees);
                } else {
                    resource.attendees = newattendees;
                }

                Calendar.Events.patch(resource, calendarId, eventId, {
                    sendUpdates: 'all',
                    sendNotifications: true
                });

                const updatedResource = Calendar.Events.get(calendarId, eventId);
                allAttendees.push((updatedResource.attendees || []).map((a) => a.email));
            })

            if (emails.every(email => existsInAll(allAttendees, email))) {
                // statusRange.setValue(`${list}`);
                inviteRange.setValue(`✅  Invited`);
            }
        }

        lock.releaseLock();
    } else {

        SpreadsheetApp.getUi().alert("Warning! The program is already running. Running two or more simultaneous sessions is not allowed. Do not click the button again until invitations are completed.");

    }

}

Campaign.html

<?!= HtmlService.createHtmlOutputFromFile('Style').getContent(); ?>
<div class="sidebar">
   <div>
      <h1 class="splash">Invite Guests</h1>
      <p>
         Guest List:
         <select class="guestlist" name="guestlist">
            <option value="Invite All">Scan Entire List</option>
         </select>
      </p>
      <p>
         Invite Type:
         <select class="statuscolumn" name="statuscolumn">
            <option value="1" selected>Column A</option>
         </select>
      </p>
      <p>
         Email Address List(s):
         <select class="inviteemailcolumns" name="inviteemailcolumns">
            <option value="3,4" selected>Column C & D</option>
         </select>
      </p>
      <p>
         <input class="button-apple" name="button-apple" value="Invite Guests" type="button-apple" onclick="inviteGuests($('.statuscolumn').val(), $('.inviteemailcolumns').val()); this.form.submit(); this.disabled=true;" onsubmit="disableButton()">
      </p>
      <p class="invite-result"><u>Do not click the button more than once or while the operation is running.</u><br><br>Inviting guests will scan each row and invite email addresses according to their label in Column C.<br><br>The label must exist in <i>Settings</i> and have invite links listed below it.<br><br><u>Remove labels for rows you do not want invited.</u><br><br>If a row fails, fix the issue and run the program again.</p>
      <!-- <p class="invite-message"><u></u></p> -->
   </div>
</div>

    
    
    <script src="//ajax.googleapis.com/ajax/libs/jquery/3.0.0/jquery.min.js">
    </script>
    
    <script>
    
    function inviteSuccess() {
      $(".invite-result").html('<font color="#008000"> Invitations Sent!</font>');
    }
    
    function inviteFailure(e) {
      $(".invite-result").html('<font color="#FF0000">⚠️ Error: ' + e.message + '</font>');
    }
    
    function inviteGuests(statuscolumn, emailcolumns){
      $(".invite-result").html('<font color="#000000">⏳ Inviting...<br><br>Please do not click the button again until completed.</font>');
      google.script.run.withSuccessHandler(inviteSuccess).withFailureHandler(inviteFailure).inviteGuests(statuscolumn, emailcolumns);
    }
    </script>

Style.html

<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<link href='https://fonts.googleapis.com/css?family=Roboto' rel='stylesheet'>

<style>

body {
    font-family: 'Roboto';
}

.subject,
.load,
.action {
    width: 250px;
}

.button-apple {
    font-family: 'Roboto';
    background-color: #1d9bd9 !important;
    border: 1px solid #1d9bd9 !important;
    color: #ffffff !important;
    -moz-border-radius: 4px;
    -webkit-border-radius: 4px;
    border-radius: 4px;
    font-size: 12px;
    font-weight: normal;
    height: 29px;
    line-height: 27px;
    margin: 0;
    min-width: 232px;
    outline: 0;
    padding: 0 8px;
    text-align: center;
    white-space: nowrap;
    cursor: pointer;
    caret-color: transparent;
}

.button-apple:hover {
    background: -webkit-gradient(linear,left top,left bottom,from(#49b4e9),to(#0293dc)) !important;
    background: -moz-linear-gradient(top,#49b4e9,#0293dc) !important;
    background: linear-gradient(top,#49b4e9,#0293dc) !important;
}

.button-apple:focus {
    border: 1px solid #1d9bd9 !important;
}


.button-stop {
    font-family: 'Roboto';
    background-color: #ff0000 !important;
    border: 1px solid #ff0000 !important;
    color: #ffffff !important;
    -moz-border-radius: 4px;
    -webkit-border-radius: 4px;
    border-radius: 4px;
    font-size: 12px;
    font-weight: normal;
    height: 29px;
    line-height: 27px;
    margin: 0;
    min-width: 232px;
    outline: 0;
    padding: 0 8px;
    text-align: center;
    white-space: nowrap;
    cursor: pointer;
    caret-color: transparent;
}

.button-stop:hover {
    background: -webkit-gradient(linear,left top,left bottom,from(#49b4e9),to(#0293dc)) !important;
    background: -moz-linear-gradient(top,#49b4e9,#0293dc) !important;
    background: linear-gradient(top,#49b4e9,#0293dc) !important;
}

.button-stop:focus {
    border: 1px solid #ff0000 !important;
}



input[type="text"] {
  margin-top: 5px !important;
}

.result,
.invite-result {
    background-color: #FFFFFF;
    border-radius: 2px;
    padding: 10px;
    font-size: 13px;
    line-height: 17px;
    text-align: center;
    border: 1px #cecece dashed;
}

.invite-message {
    background-color: #FFFFFF;
    border-radius: 2px;
    padding: 10px;
    font-size: 13px;
    line-height: 17px;
    text-align: center;
    border: 1px #cecece dashed;
}

}
hr.style-four {
    margin: 15px 0px;
    height: 15px;
    border: 0;
    border-top: 2px #e0e0e0 dotted;
}

.sidebar {
padding: 10px 17px;
    position: relative !important;
    width: auto !important;
}

 h1.splash {
    font-size: 22px;
    font-weight: bold;
    letter-spacing: 0px;
    color: #1d9bd9;
    padding: 5px 0px 8px 5px;
    margin-bottom: 18px;
    border-bottom: 2px #e8e8e8 dotted;
    text-align: left;
 }
 
</style>

---