CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmailgoogle-sheets-apigmail-api
Gitz
Gitz

Reputation: 63

Unable to clear the contents in the sheet while using Sheets API in Google Appscript

I am using the following code to fetch table from Gmail to my sheet.

var labelName = "label"

function getEmailss_(labelName, sheetName) {
    var label = GmailApp.getUserLabelByName(labelName);
    if (!label) {
        console.log("No label found that match the search query: " + labelName);
        return
    }
    var threads = label.getThreads();
    if (threads.length == 0) {
        console.log("No threads found under label: " + labelName);
        return
    }
    var msg = threads[0].getMessages()[0];
    var arrStr = msg.getBody();
    var table = arrStr.match(/<table[\s\S\w]+?<\/table>/);
    if (!table) {
        console.log("No tables found in the email ");
        return
    }
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    var requests = [{ pasteData: { html: true, data: table[0], coordinate: { sheetId: sheet.getSheetId() } } }];
    Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

function saveEmailsss() {
    var sheetName = "Shhet1"; 
    getEmailss_(labelName, sheetName);
}

The code works very well, the issue is with using ".clear()" in the code. I want the existing data to be cleared before adding the new output. So the additional line of code I have added is sheet.getRange("A1:E").clearContent(); before var requests. However, upon running, it's not retaining the data from the table, but only the formatting. Any help on this would be highly appreciated. PS: As a temp workaround, I am using deletecolumns and addcolumns instead of clearContent.

This is how the output looks like after using clearContent.

enter image description here

Upvotes: 1

Views: 61

Answers (1)

Tanaike
Tanaike

Reputation: 201418

In your situation, when you want to use clear() before you run Sheets.Spreadsheets.batchUpdate, how about modifying it as follows?

From:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var requests = [{ pasteData: { html: true, data: table[0], coordinate: { sheetId: sheet.getSheetId() } } }];
Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());

To:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);

sheet.getRange("A1:E").clear(); //  or sheet.clear(); // Added
SpreadsheetApp.flush(); // Added

var requests = [{ pasteData: { html: true, data: table[0], coordinate: { sheetId: sheet.getSheetId() } } }];
Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());

Upvotes: 2

Related Questions