The subject line of the email is summarized in a table as a progress table to save time!

2020.7.5

I send a monthly progress report of my work to a particular contractor I have a relationship with. But sending them the progress is very annoying.

So here are my ways to automate it as much as possible.

Using email to summarize the progress of the work

I usually use Gmail for business correspondence, and I forward emails from my company’s domain to and from Gmail.

For correspondence with vendors, you’ll most likely use email mail for practical purposes.

That’s why I use Gmail’s label feature to summarize the progress of our work.

Specifically, I write the subject line of the email in such a way that it doubles as the progress of the work. Here’s how to do it.

I’ll write a separate post on how to write the subject line of an email. In this article, we will describe how to automate the process.

1. Label outgoing and incoming emails

Label your outgoing and incoming emails. Gmail has a filter feature that allows you to put a unique label name on your outgoing and incoming messages when you receive them. Please put a label on it.

Also, you should make it easy to understand, since you will summarize the contents of the mail based on these label names.

2. Summarize the contents of an email with a specific label.

Summarize the subject and send-receive times of emails with specific labels in a Google spreadsheet.

3. Create a spreadsheet.

Create a new Google spreadsheet and create a template with the time in the first column and the subject line of the email in the second column.

4. Add a program to the spreadsheet

Select the Script Editor in the Tools menu.

Paste the following code. This code summarizes the subject and time of the emails from the 1st to the 30th of that month in a spreadsheet Program.

function writeGmailToSpreadSheet() {
  var date = new Date();
  date.setDate(1); //Get the first day of the date it was launched
  var row = 2; // The first row is for the header, so from row 2 onwards
//Select Sheet

var sheet = SpreadsheetApp.getActiveSheet();
//Get the information from the 1st to today (30th) of the relevant day.

var searchQuery = label:zapier after:${ Utilities.formatDate(date, 'JST', 'yyyy/M/d ')}`;

var threads = GmailApp.search(searchQuery);
for(var n in threads){

var thread = threads[n];

var msgs = thread.getMessages();

for(m in msgs){

var msg = msgs[m];

var maildate = msg.getDate();

var subject = msg.getSubject();

You can get the value of this object by calling sheet.getRange(row,1).setValue(Utilities. formatDate(maildate, 'Asia/Tokyo', 'yyyy-MM- dd HH:mm:ss'));

sheet.getRange(row,2).setValue(subject);

row++;

}

}

}

5. Perform timed execution.

After entering the above program, click on the clock symbol (trigger) on the program screen to specify the time execution to run automatically on the 30th of each month.

Click on the clock (trigger) on the program screen and set up as follows. However, the above program will retrieve the subject line of the email from the 1st to the date of execution, so please set the 30th of each month as follows I think it would be a good idea to set it for the day.

6. Send an email.

After the above program is automatically executed, another program will automatically send an email to a specific person in charge. The text of the email is stored in advance and the table in the Google spreadsheet is also shared with you. You should set it up so you can see it.

I’ll do a separate post on how to send an email.

Finally.

What did you think?

I’m discovering that you can automate a lot more things than you might think by using Gmail and various Google office apps.

I’d love to automate the tedious tasks I do every month to make the most of my free time.

[itemlink post_id=”2050″]