Connect Gmail to Google Spreadsheets using Apps Script

A recent hangout I did. Take a look! It shows how Apps Script can access Gmail. It's really neat. In this example we create a spread sheet and have it display emails from your inbox. It'll also highlight the messages that are starred. We'll come back to this example when we learn how to connect contact forms to spread sheets. Hope you enjoy.

Source Code

Here's the source code for the above mentioned project.

function myFunction() {
  
  // Tells Apps script we want to edit the current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Select the first sheet in the document
  var sheet = ss.getSheets()[0];
  
  // Get the data from your inbox
  // Here you can see we only get the first 15 emails
  var threads = GmailApp.getInboxThreads(0,15);
  
  // Selects C3 of our current sheet
  cell = sheet.getRange("C3");
  var cellValue = "";
  
  // Now we are going to loop over every item in our thread list
  for (var i = 0; i < threads.length; i++) {
    
    // Reset cell background colors to white
    // We do this because later we'll highlight starred emails
    cell.offset(0,0).setBackgroundColor("White")
      
    // Number next to email - add one because array starts at zero
    cell.offset(i,-1).setValue(parseInt(i)+1);
    
    // Get subject from email 
    var cellValue = threads[i].getFirstMessageSubject();
    cell.offset(i,1).setValue(cellValue+1);
    
    // Retrieves first message from thread and saves to cell 
    tMessage = threads[i].getMessages();
    var emailFrom = tMessage[0].getFrom();
    cell.offset(i,0).setValue(emailFrom);
    
    // If thread contains a star then make yellow
    if(threads[i].hasStarredMessages()) {
      cell.offset(i,0).setBackgroundColor("Yellow");

    }
    
  }
  
  sheet.getRange("C1").setValue("Unread Emails: " + GmailApp.getInboxUnreadCount());
}