Google Apps Script to count number of emails received yesterday, that has certain label, then save # daily to spreadsheet

Question

Basically what the title says, but I want to:

1) Automatically count the number of emails received to my gmail account, that has a certain label on it

2) Count once everyday, even if zero

3) And report daily to a Google Spreasheet

4) So I can make a monthly report like such:

Date / #
Date / #
Date / #
.
.
.
Total for October / #
Average per day / #

I'm sure this is piece of cake using Google Script for script gurus, but I have no clue. Please teach me!

Answer 1

Open a new Untitled spreadsheet and go to Tools -> open Script editor and paste the code given below.

function CountEmail() 
{
var label = GmailApp.getUserLabelByName("LabelName");
var labelname = label.getName();
var mails = label.getThreads();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var date = new Date();
sheet.appendRow([labelname,date,mails.length]);
}

To run the script daily you will need to set a project trigger in the app script.For that follow the steps given below:

Resources -> Current Project's Trigger -> Add trigger -> Time driven -> Hour timer -> select the time when you want the script to run.

The total number of emails and the average emails/day can be calculated in the spreadsheet itself by just using the Sum() function.

Answer 2

Referencing the code Suyash Gandhi has posted (citing it here in case it gets removed so there is no confusion).

NOTE: not my code!!! Credit to Suyash Gandhi
function CountEmail() 
{
  var label = GmailApp.getUserLabelByName("LabelName");
  var labelname = label.getName();
  var mails = label.getThreads();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var date = new Date();
  sheet.appendRow([labelname,date,mails.length]);
}
NOTE: not my code!!! Credit to Suyash Gandhi

See the picture below

What you see here are 2 threads, 1 email in the bottom one, 3 emails in the top one and 1 draft in the top one. That given code will return a 2 here. If you wish to count all 4 (or 5 if you want the draft) you will need to use Gmail API (see reference here).

I have a script that gets all emails (every message) for a specific time period and outputs the receive date, sender, recipient and title to a spreadsheet. This is the code that actually fetches the emails. The rest of the code is mostly creating files, generating the query string and reset the script if it runs too close to 6 minutes.

queriedMessages =
      Gmail.Users.Messages.list(userInfo.mail,
                                {
                                  'q': queryString,
                                  'pageToken': execProperties.nextPageId
                                });

• userInfo.mail is the email address you are fetching the emails from. This is simply written like this because the script can be run with any account
• queryString is a string that is used to search for emails and is exactly the same as you use in the gmail search box. So you would have label:labelname
• pageToken is a code of the page of the search (basically what is needed when you click the next page button in gmail). It is returned as part of this function so you would be able to access it from queriedMessages.nextPageToken. So if you get more than 1 page, then you will need it to access the rest of the messages.

Also, keep in mind that you are getting all the messages fitting the query, so if you do this daily, you may want to include a trigger. Also, keep in mind that functions firing from triggers ignore your timezone (known bug), but you can figure out how to create a query that works for only 1 day fairly easily. Personally I just grab +1 day on the beginning and the end and just filter those messages out.