CODEWITHSUNDEEP
emailgoogle-apps-scriptautomationgoogle-sheets
Andrew Anderson
Andrew Anderson

Reputation: 1126

How to add a line to a google spreadsheet via email?

I need a google apps script which can do the following: if I send an email to my alias email, say to [email protected], it adds a line in a specific google spreadsheet with data from that email. E.g. timestamp, subject, the first line of a body - whatever.

I found interesting article describing similar process of sending data to google spreadsheet.

May be there is no any direct way of doing that, however, it should exist some workaround.

P.S. It should be done only by using a google echosystem. No php, servers etc.

Upvotes: 3

Views: 1567

Answers (1)

oshliaer
oshliaer

Reputation: 4979

There are two step for this.

#1

At first create a trigger to run the mail checker.

function switchTrigger() {
    var isExist = false;
    var triggers = ScriptApp.getProjectTriggers();
    for (var i = 0; i < triggers.length; i++) {
        if ( /*<CONDITION>*/ ) {
            isExist = true;
            ScriptApp.deleteTrigger(triggers[i]);
        }
    }
    if (!isExist) {
        ScriptApp.newTrigger( /*<CONDITION>*/ ).create();
        Logger.log('create');
    }
}

#2

Then you have to check emails. Something like that

function checkMail() {
    var sh = SpreadsheetApp.openById(properties.targetSheetId).getSheets()[0];
    var query = /* properties.queryString */ ;
    var threads = GmailApp.search(query);
    if (threads.length < 1) return;
    for (var i = 0; i < threads.length; i++) {
        var messages = threads[i].getMessages();
        for (var j = 0; j < messages.length; j++) {
            if (messages[j].isStarred()) {
                sh.appendRow([
                    new Date(),
                    messages[j].getFrom(),
                    messages[j].getPlainBody()
                ]);
                messages[j].unstar();
            }
        }
    }
}

Be careful. You have to set up Gmail filters so that all incoming to [email protected] would be marked with an star at the beginning.

Working Example

Upvotes: 2

Related Questions