How to send Email automatically from Google Sheet via C#

Cover Image

Project Introduction

The project provide to send one times the email which have the Google Sheet. The mail was sent one times for each mail address by project.

How does the project work ?

I explain step by step:

• Get the all data from Google Sheet API
• Record all data to the database
• Send mail to which have never sent
• Update data which was sent
• Repeat all operation every 1 minute

How to get the all data from Google Sheet API

First we should go console.cloud.google.com and enable the google sheet API. Then save the credentials.json file for security in the project folder. I take the video for this section which how to use google sheet API. Just first 5 minute of video watching is enough for got it. In C# we use Google.Sheet.API.v4 library. We create new class such as GoogleSheetAPIHelper.cs

class GoogleSheetAPIHelper
{
protected static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
protected static readonly string ApplicationName = "MailBot";
protected static readonly string SheetId = "YOUR_SHEET_API";
static public IList<IList<Object>> GetData()
{
GoogleCredential credential;
using (var stream =new FileStream("credentials.json",
FileMode.Open, FileAccess.Read)){
credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
string spreadsheetId = SheetId;
string range = "SHEET_NAME";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = request.Execute();
IList<IList<Object>> values = response.Values;
if (values != null && values.Count > 0)
{
return values;
}
else
{
return null;
}
}
}

You should write your google sheet id which is unique and located middle of url.

google sheet API url

In this image your google sheet id is 1Z_f1UNhNrOhYddiVehyMa_mHiROHXvHn5ebwXlHGSmI. Then we create mapper class such as GoogleSheetAPIMapper.cs file. But for this first we should create database model. That’s why we create /models/MailModel.cs file.

class MailModel
{
public int Id { get; set; }
public string  FullName { get; set; }
public string Email { get; set; }
public bool IsEmailConfirmed { get; set; } = false;
}

This model might be changeable according your sheet and data. But last property will stay as constant. So now we can continue with our mapper class.

class GoogleSheetAPIMapper
{
public static List<MailModel> MapperFromService()
{
var items = new List<MailModel>();
IList<IList<Object>> obj = GoogleSheetAPIHelper.GetData();
obj.RemoveAt(0);
foreach(var item in obj)
{
Console.WriteLine(item[2]);
MailModel mailModel = new MailModel() { FullName=item[0].ToString(),
Email=item[2].ToString()};
items.Add(mailModel);
}
return items;
}
}

I suppose that everyone ask that why we delete element which index is 0. Because this element is column name, not the data.

How to record all data to the database ?

Actually I use Sqlite database but it depends on your choices. That’s why I didn’t write about configuration of database. Also I use EntityFramework library. We create DatabaseController.cs class. We need 4 methods:

• Recognize this mail exist on table or not
• Add mail
• Update table which sent
• Find unsent mails

public async Task<bool> FindByEmail(string email)
{
using (var db = new SqliteDbContext())
{
var current = from user in db.Mails
where user.Email == email
select user;
if (await current.FirstOrDefaultAsync() == null)
{
return false;
}
return true;
}
}

We need this methods because we don’t want add same mail more than one times.

public async Task AddAllEmails(List<MailModel> obj)
{
using (var db = new SqliteDbContext())
{
foreach (var email in obj)
{
if (!await FindByEmail(email.Email))
{
await db.Mails.AddAsync(email);
}
}
await db.SaveChangesAsync();
}
}

We will return here for other 2 methods. But now we focus addition part. So we can map and add for result we should connect them. We create GoogleSheetController.cs class.

class GoogleSheetController
{
public static async Task AddEmailFromAPI()
{
List<MailModel> obj = GoogleSheetAPIMapper.MapperFromService();
DatabaseController databaseController = new DatabaseController();
await databaseController.AddAllEmails(obj);
}
}

How send mail to which have never sent ?

We use System.Net.Mail library for this. We create MailServiceHelper.cs file.

class MailServiceHelper
{
public static void SendEmail(string mailAddress, string fullName)
{
MailMessage message = new MailMessage();
SmtpClient smtp = new SmtpClient();
message.From = new MailAddress("your mail");
message.To.Add(new MailAddress(mailAddress));
message.Subject = "Information";
message.Body = $"Thank {fullName} for participate our forum.";
smtp.Port = 587;
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential("your mail", "your password");
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.Send(message);
}
}

You can configure this part with your mail address and password. So we can return other 2 methods of DatabaseController.cs class.

public List<MailModel> FindUnsendedEmail()
{
using (var db = new SqliteDbContext())
{
var current = from user in db.Mails
where user.IsEmailConfirmed == false
select user;
return current.ToList();
}
}
public async Task UpdateEmail(int Id)
{
using (var db = new SqliteDbContext())
{
var current = from user in db.Mails
where user.Id == Id
select user;
if (await current.FirstOrDefaultAsync() != null)
{
current.FirstOrDefault().IsEmailConfirmed = true;
await db.SaveChangesAsync();
}
}
}

So actually everything are done. In finally we schedule all these methods for involve every 1 minute.

How to repeat all operations every 1 minute ?

We use quartz.net library for schedules. Firstly we should initialize our schedule job, that’s why we create schedule.cs class.

class Scheduler
{
public static async Task SchedulJob()
{
StdSchedulerFactory factory = new StdSchedulerFactory();
IScheduler scheduler = await factory.GetScheduler();
await scheduler.Start();
IJobDetail job = JobBuilder.Create<SchedulerJob>()
.WithIdentity("jobName", "jobGroup")
.Build();
ITrigger trigger = TriggerBuilder.Create()
.WithIdentity("triggerName", " triggerGorup")
.StartNow()
.WithSimpleSchedule(x => x
.WithIntervalInMinutes(1)
.RepeatForever())
.Build();
await scheduler.ScheduleJob(job, trigger);
}
}

You can see ScheduleJob class which locates in our tasks. So we create ScheduleJob.cs class.

class SchedulerJob : IJob
{
public async Task Execute(IJobExecutionContext context)
{
Console.WriteLine("Starting...");
await GoogleSheetController.AddEmailFromAPI();
await MailController.SendAllEmail();
Console.WriteLine("Ended...");
}
}

Finally we add last method to our main method.

class Program
{
static async Task Main(string[] args)
{
await Scheduler.SchedulJob();
Console.ReadLine();
}
}

Conclusion

We finished our project. You can easily run and test your project.

Get the full code on the GitHub Repository.

Thank you for reading and I hope you enjoyed it. Any question, suggestions let me know in the comments below! See you soon…