0

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!

Neutron80
  • 1
  • 1
  • 2
  • Welcome to stackoverflow. to make your question valid show us your code and whats not working. a specification is not a valid question. – Zig Mandel Oct 16 '16 at 17:42

2 Answers2

3

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.

Suyash Gandhi
  • 926
  • 6
  • 24
  • Thank you Suyash, I'll try this out ASAP! – Neutron80 Oct 18 '16 at 01:23
  • 1
    Suyash, I finally found the time to set this up. Do I need to label the columns a certain name, or could the sheet be completely blank? Either way I'm going to let this run for a few days, see how it works out. – Neutron80 Oct 20 '16 at 15:05
  • Additional question: does the email need to be left in the inbox for this to work? Does being read/unread matter to the count? – Neutron80 Oct 20 '16 at 15:14
  • 1
    The sheet can be completely blank...it wont affect the working of the script if you do not label the columns, but for your understanding you can label them. As long as the mail has the label you're searching for it wont matter where the mail is...and read/unread does not change the actual count. – Suyash Gandhi Oct 25 '16 at 09:24
  • Any chance you can tell me how to do this to a delegated (alias) account? – DanCue Mar 16 '23 at 22:20
1

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

enter image description here

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.

Vytautas
  • 2,238
  • 1
  • 9
  • 20
  • The original question as asked by @Neutron80 states that he wants to count the number of emails "received" to his Gmail account. What you said here is correct but by definition of the question the output of what you've suggested would be wrong as the thread might also consist of replies that the user has sent, and obviously those emails should not be counted as received emails. To count the number of emails you'll have to check the whether the email is received or sent,which will again add a few lines to the code. But all this said and done, it all depends on what the actual requirement is. – Suyash Gandhi Oct 18 '16 at 09:57
  • @SuyashGandhi the approach I provide let's you customise what messages you want. As I sad, it's the same as using the search box in the Gmail UI. So if for example you **receive** 4 emails and **send** 1 that are all in 1 thread (let's say 2 people replied to each other without you at first), then, as per your answer, it would still be counted as 1, whereas in my answer it would be 4. as you can simply exclude the message you sent yourself (simple `-from:me` in the string should do it). But as we can all agree. Depends wether you want to count conversations or actual emails. – Vytautas Oct 18 '16 at 11:00
  • Vytautas , Suyash, my apologies, I am so new to this, I have practically no clue what you guys are discussing about. I'm trying out Suyash's method for starters, and will report back the outcome. Thank you both for shedding your light! – Neutron80 Oct 20 '16 at 15:07