How can I collect all of the data on a Google spreadsheet and then clear it after the script has been run?

Question

I am creating a script that will grab all unread e-mails, move the bodies of these e-mails into a Google spreadsheet, push all of this data through to Slack via webhook, then clear the sheet.

The first part of the script that grabs the unread e-mails and puts it in the appropriate cells is working fine. The part where it is pushing the data through is not working. I have it set to a loop to make sure that it grabs all of the data, but it is not working. I have tried to make it not show up as an array, but that didn't seem to work either.

var SEARCH_QUERY = "label:inbox is:unread to:me";

function getEmails_(q) {
    var emails = [];
    var threads = GmailApp.search(q);
    for (var i in threads) {
        var msgs = threads[i].getMessages();
        for (var j in msgs) {
            emails.push([msgs[j].getBody().replace(/<.*?>/g, '\n')
                .replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')
            ]);
        }
    }
    return emails;
}

function appendData_(sheet, array2d) {
    sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function saveEmails() {
    var array2d = getEmails_(SEARCH_QUERY);
    if (array2d) {
        appendData_(SpreadsheetApp.getActiveSheet(), array2d);
    }
}

function postToSlack() {
    var url = "SLACK WEBHOOK URL HERE";
    var sheet = SpreadsheetApp.getActiveSheet();
     // This represents ALL the rows
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
    var row = [];
    for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row.push(values[i][j]);
     }
   }
   Logger.log(row);

    var str = values;
    var payload = {
        "text": str  
    }



    var post = {
        "method": "post",
        "payload": JSON.stringify(payload)

    };



    return UrlFetchApp.fetch(url, post);
}

}

I expect that the data in the cells will push right out into Slack, but nothing is happening. When I run the Logs for the rows that it finds, it is also pulling up no data.

I assume `saveEmails()` is the function you are calling to run the script. Did you forget to call `postToSlack()`? For that matter, why go through all of this instead of simply forwarding the emails...? — sinaraheneba, May 17 '19 at 05:42
I called both functions, and nothing is happening when I call the second. The reason I can't forward the e-mails is because they always show up in an embedded format on Slack (like you have to click to expand the email) and that's not what I want. — Crystal Zhao, May 17 '19 at 15:57

Aung49 · Answer 1 · 2019-05-18T17:22:03.307

0

Edit: In your added answer, you can either delete clear() and add in sheet.getRange("A1:A40").clearContent(); below Logger.log(row);. Another way is that you can also take out the clear() out of the saveEmails() and call like this,

function saveEmails() {
var array2d = getEmails_(SEARCH_QUERY);
if (array2d) {
    appendData_(SpreadsheetApp.getActiveSheet(), array2d);
}
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var row = [];
for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
        if (values[i][j]) {
            row.push(values[i][j]);
        }
    }

}
Logger.log(row);

var str = values.toString();
var payload = {
    "text": str
}

clear();
//    var post = {
//        "method": "post",
//        "payload": JSON.stringify(payload)
//      
//    };


//    return UrlFetchApp.fetch(url, post); 
}

function clear() {
    var app = SpreadsheetApp;
    var sheet = app.getActiveSpreadsheet().getActiveSheet();
    sheet.getRange("A1:A40").clearContent();
}

edited May 18 '19 at 17:22

answered May 17 '19 at 08:34

Aung49

157
8

I moved it to a different part of the code, but now the issue is that the only thing the code is returning is the word "Array" and nothing else. – Crystal Zhao May 17 '19 at 16:13
If I put the clear before the actual post, will it still post the information to Slack before it clears it? It looks like there would be no data to put into Slack based on the sequence you put here. In your alternate code, commenting out the return line with the URLFetchApp.fetch seems like it won't do the step of putting it into Slack at all. – Crystal Zhao May 20 '19 at 12:27
The URLFetchApp.fetch was commented out in my code because your edited code also had it commented out. If you want it to post, just un-comment it and add whatever necessary to the post method to post it. Regarding the `clear()` function, you might want to call the `clear()` at the end after posting. This will make sure that the data is only cleared after a successful post run. – Aung49 May 21 '19 at 00:30

score 0 · Answer 2 · answered May 17 '19 at 19:01

I figured it out! It wanted a toString() command. The following works perfectly EXCEPT that it does not clear the sheet at the end. It will only clear if a separate function is running.

var SEARCH_QUERY = "label:inbox is:unread to:me";
var url = "SLACK WEBHOOK URL";

function getEmails_(q) 
{
    var emails = [];
    var threads = GmailApp.search(q);
    for (var i in threads) {
        var msgs = threads[i].getMessages();
        for (var j in msgs) 
        {
          emails.push([msgs[j].getPlainBody().replace(/<.*?>/g, '\n')
                .replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')]);
            msgs[j].markRead();   
        }
    }
      return emails;
}

function appendData_(sheet, array2d) {
    sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function saveEmails() {
    var array2d = getEmails_(SEARCH_QUERY);
    if (array2d) {
        appendData_(SpreadsheetApp.getActiveSheet(), array2d);
}
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getDataRange();
    var values = range.getValues();
    var row = [];
    for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row.push(values[i][j]);
     }
    }

   }
   Logger.log(row);

    var str = values.toString();
    var payload = {
        "text": str  
    }



//    var post = {
//        "method": "post",
//        "payload": JSON.stringify(payload)
//      
//    };



//    return UrlFetchApp.fetch(url, post); 




function clear() {
  var app = SpreadsheetApp;
  var sheet = app.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1:A40").clearContent();}




  }

How can I collect all of the data on a Google spreadsheet and then clear it after the script has been run?

2 Answers2

Linked