CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmailquota
Kevin Hall
Kevin Hall

Reputation: 23

Google Sheets Email Quota Limitation

I am running a fantasy sports draft with my friends through a spreadsheet. When a draft pick is made, I want the league to be notified of this event.

So I've developed a Google Apps Script that e-mails the league when a draft pick is made. The problem is I have 24 league members so after 4 draft picks I reach my 100 recipient email quota. I was hoping the recipient quota was limited to NEW recipients but it appears it is total recipients. Does anyone know a work around?

I know I could invest money into a g-suite account but this is a free league so I'm not looking to spend money

function submitPick() {
  var draftSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2019_draft");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test_email");
  var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Manager Info");

  SpreadsheetApp.flush();
  //Choose email addresses from 'manager info' tab
  var recipient = emailSheet.getRange(2, 6, 25, 1).getValues();
      Logger.log(recipient);
  //get array of all picks on "draft test" tab (find the latest draft pick)
  var picks = draftSheet.getRange(5,23,240,1).getValues();

    for (var i=0; i<picks.length; i++){
    if (picks[i] == ""){
      var latestPick = i;
      break;
    }
   }
  //define subject and body of email to be sent
  var subject = sheet.getRange(i+1, 2).getValue();
  var body = sheet.getRange(i+1, 1).getValue();
  MailApp.sendEmail(recipient, subject, body);
}

Upvotes: 0

Views: 142

Answers (2)

Wicket
Wicket

Reputation: 38200

The "Google" solution is to use Google Groups.

  1. Create a Google Group.
  2. Add the recipients directly as members, invite by email to join or ask them to join by themselves.
    • The limits vary depending if you are using a consumer group or Google Groups for Business (G Suite account), but if the future recipients join by themselves the daily limit is "pretty high" (~ 1000 daily new members joined by themselves, the last time I research it, it was not officially disclosed to prevent abuses).
  3. On your Google Apps Script code use the group email address as the recipients address. It will count as one email recipient.

Upvotes: 2

TheAddonDepot
TheAddonDepot

Reputation: 8964

Here's an approach I've used for past projects.

You could setup a Forever Free account with Mailchimp. They allow up to 2000 recipients at 12,000 emails a month. You then have your participants sign-up via a MailChimp form. Then you can leverage Mailchimps API to send messages to your subscribers from Google Apps Script. You'll have to spend some time learning how to use Mailchimp's API but its worth learning.

Upvotes: 1

Related Questions