Google App Script to parse specific emails and write to Google Sheets

gistfile1.js

// Modified from http://pipetree.com/qmacro/blog/2011/10/automated-email-to-task-mechanism-with-google-apps-script/

// Globals, constants
var LABEL_PENDING = "pending";
var LABEL_DONE = "done";

// processPending(sheet)
// Process any pending emails and then move them to done
function processPending_(sheet) {
  
  // Get out labels by name
  var label_pending = GmailApp.getUserLabelByName(LABEL_PENDING);
  var label_done = GmailApp.getUserLabelByName(LABEL_DONE);

  // The threads currently assigned to the 'pending' label
  var threads = label_pending.getThreads();

  // Process each one in turn, assuming there's only a single
  // message in each thread
  for (var t in threads) {
    var thread = threads[t];

    // Gets the message body
    var message = thread.getMessages()[0].getPlainBody();
    
    // TODO: Process the messages here
    
    // Add message to sheet
    sheet.appendRow([message]);
    
    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }
}

// main()
// Starter function; to be scheduled regularly
function main_emailDataToSpreadsheet() {
  // Get the active spreadsheet and make sure the first
  // sheet is the active one
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.setActiveSheet(ss.getSheets()[0]);

  // Process the pending emails
  processPending_(sh);
}

Hi azkhalidtruth,

I'm not sure if I can help you in this case. I can make guess on how it can work though.

I'm assuming the content you want is inside the email message.

Parsing the message can be tricky. It depends on how consistent the content is. If there's a consistent pattern, you can try to use regular expressions. Apps Script is similar to javascript I think.

Here is a StackOverflow example on how to use Regular Expressions: https://stackoverflow.com/questions/17573598/google-apps-script-regular-expression-to-get-the-last-name-of-a-person

var message = thread.getMessages()[0].getPlainBody();

// TODO: Process the messages here using regular expressions to extract bookings date, time, location, and contact.

// You can add the extracted data to spreadsheet by appending to the row --something like this
sheet.appendRow([message]);
sheet.appendRow([booking_date]);
sheet.appendRow([time]);
sheet.appendRow([location]);
sheet.appendRow([contact]);

You can also try asking ChatGPT to write the code to parse the email. Not sure if it will work or not. But worth trying.