August 18, 2023

Scrape attachments from gmail and write into Google sheets using python

The best time to establish protocols with your clients is when you onboard them.

Heading

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

This article helps you write the code to scrape CSV attachments from Gmail and write the data into Google Sheets.

We will approach this problem in two steps.

  1. Scrapping the attachments from Gmail
  2. Writing the data into Google Sheets

Scrapping the attachments from Gmail

Importing the necessary Python libraries should come first. Imbox will be used to read IMAP emails from Gmail, pandas to read CSV, googleapiclient to access Google Sheets, and google.oauth2 to set up a Google Service Account. IMAP is an Internet standard protocol used by email clients to retrieve email messages from a mail server. We’ll additionally make use of additional supporting libraries. Below is a list of every import.

First, let’s sign in to Gmail.

The following code logs into your email account using imbox by using your “Username” and “Password.”

We will be utilizing the imap.gmail.com server as we are testing this on a Gmail account. Click this link to view a list of IMAP servers for the most popular email providers.

Additionally, if you use a Gmail account, make sure you grant apps access to your account.

Let’s start with IMAP configuration.

imap connection setting

Connecting to Gmail with uaername and password

We will also specify the directory (download_folder) to download the attachments using the python OS package. We should also specify the subject that we need to search for.

We will now loop through all the messages with the specified subject (Your_subject) and download the attachments into the download_folder path. We will then convert the downloaded files into CSV format using stringIO function.

Writing data into google sheets

We have to now follow a 2 step process to write data into Google Sheets.

  1. Create a google sheet and take the ID from the URL
  2. Create the Client ID and Client Secret from the developer console
ID is highlighted in yellow

After opening the developer console, we need to create a project in the developer console.

We need to click on the new project and fill in the required fields.

We must select Credentials from the left side menu bar after creating the project. The service accounts ought to be visible in credentials. By selecting manage service account, we must create a new service account.

We then have to create a service account by clicking on the Create Service Account

We need to fill in the service account details to complete the create service process

On successful creation of the service account, an email with for the service account will be created.

Share the google sheet with the service account Email ID and give the editor access.

After that, we need to click on the Keys menu to create a service account JSON file. Finally, we will download the JSON file.

Now let's specify the JSON file in a variable called keys and specify the spreadsheet ID in Spreadsheet_id.

And finally, we will write the data into the spreadsheet using the following code.

CodeStax.Ai
Profile
August 16, 2023
-
6
min read
Subscribe to our newsletter
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Share this article:

More articles