How import data from gmail txt attachment into google sheet

Question

I receive a daily email with a .txt attachment and I wrote a script that gets finds the file and brings it into a Google sheet which I hope to run on a daily trigger.

I used someone else's script that oringinally had a .csv file as the attachment. How do I make it work for a txt file with a "~" delimiter????

function importsearchresultsTXTFromGmail() {

var threads = GmailApp.search("from:****@erusd.org");
var messages = threads[0].getMessages();
var message = messages[messages.length - 1];
var attachment = message.getAttachments()[0];

// Is the attachment a TXT/CSV file

attachment.setContentType('text');
//attachment.setContentTypeFromExtension();

if (attachment.getContentType() === "text") {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sh0 = sheet.getSheetByName("Data")
var csvData = Utilities.parsecsv(attachment.getDataAsString(), "~");


// Remember to clear the content of the sheet before importing new data
sh0.getRange(3, 1, csvData.length, csvData[0].length).setValues(csvData);
GmailApp.markMessageRead(message);

}
}

Answer 1

Getting Attachment on Gmail, Process TXT and Paste to Google Sheets

Thank you for continuously responding. There are 3 parts in this code. Which Highlighted on the comments on the code. Please make sure to read it so you can adjust the values as well accordingly. Getting TXT doesn't need to be parsed, you can use it directly by getting it as a string. Structuring the Data is really crucial with Google Sheet.

Sample Code:

function importsearchresultsTXTFromGmail() {
var dataToPaste = [];
var threads = GmailApp.search("[email protected]");
var messages = threads[0].getMessages();
var message = messages[messages.length - 1];
var attachment = message.getAttachments()[0];
/* I copied your parameters on getting your desired attachment please feel free to change it depending on your needs*/

//Getting the content directly from the text file no need to parse it.
content = attachment.getDataAsString();


// I am just getting your active Spreadsheet, please change the Sheet Name accordingly

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");

// In this part I have split and remove unneccesarry parts of your array based on your sample data

var splittedArr = content.split("~");
splittedArr.pop();

// Your sample data set has 24 elements, so I have change chunk one whole array to cover per row pasting of data
for(var i = 0; i < splittedArr.length; i += 24){
  dataToPaste.push(splittedArr.slice(i,i + 24))
}

// Getting the structure of resulting array and pasting the data as 2D array to be handled by sheet.
var range = sheet.getRange(sheet.getLastRow()+ 1, 1, dataToPaste.length, dataToPaste[0].length)
range.setValues(dataToPaste);
}

How the result Looks Like:

References:

Gmail Attachments || getDataAsString()

Array Split

Google Sheet || setValues()