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

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.

CodePudding user response：

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());