数字表行在谷歌传播使用应用程序脚本与文件html在谷歌驱动器

Question

我是新手，我使用谷歌脚本能够从我的谷歌驱动器的html文件导入表。但是现在我有一个问题，我不知道如何检查输入的数据是否是最新的数据。希望大家都能帮忙。我的代码使用:

function myFunction2() {
var fileId = "1UqMZe3FlUWgqslL2lH6AjrwgDDYYzH9s";  
var spreadsheetId = "1ezNNoHNIalACU0gYiKSFrTkSXJNJewekRf0mP6yCWWc"; 
var sheetName = "Raw1";  
var ss = SpreadsheetApp.openById(spreadsheetId);
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var rowIndex = 0;
if (sheet.getLastRow() == 0) {
// Retrieve tables from HTML data.
var html = DriveApp.getFileById(fileId).getBlob().getDataAsString();
var values = html.match(/<table[wsS]+?</table>/gi);
// Put the HTML tables to the Spreadsheet.
values.forEach(function (e) {
var resource = { requests: [{ pasteData: { html: true, data: e, coordinate: { sheetId: sheetId, rowIndex: rowIndex } } }] };
Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
rowIndex = sheet.getLastRow();

})
} else {
sheet.clear();
}

}

Answer 1

如果您想在运行脚本之前清理表单，可以很容易地完成:

function myFunction2() {
var fileId = "1UqMZe3FlUWgqslL2lH6AjrwgDDYYzH9s";  
var spreadsheetId = "1ezNNoHNIalACU0gYiKSFrTkSXJNJewekRf0mP6yCWWc"; 
var sheetName = "Raw1";  
var ss = SpreadsheetApp.openById(spreadsheetId);
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var rowIndex = 0;
sheet.clear(); // clean the sheet <------------- HERE
// Retrieve tables from HTML data
var html = DriveApp.getFileById(fileId).getBlob().getDataAsString();
var values = html.match(/<table[wsS]+?</table>/gi);
// Put the HTML tables to the Spreadsheet.
values.forEach(e => {
var resource = { requests: [{ pasteData: { html: true, data: e, coordinate: { sheetId: sheetId, rowIndex: rowIndex } } }] };
Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
rowIndex = sheet.getLastRow();  
})
}

或者在清理之前检查html文件是否有表是有意义的:

function myFunction2() {
var fileId = "1UqMZe3FlUWgqslL2lH6AjrwgDDYYzH9s";  
var spreadsheetId = "1ezNNoHNIalACU0gYiKSFrTkSXJNJewekRf0mP6yCWWc"; 
var sheetName = "Raw1";  
var ss = SpreadsheetApp.openById(spreadsheetId);
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var rowIndex = 0;
// Retrieve tables from HTML data
var html = DriveApp.getFileById(fileId).getBlob().getDataAsString();
var values = html.match(/<table[wsS]+?</table>/gi);
// Check if the html file has any tables
if (values.length == 0) { 
SpreadsheetApp.getUi().alert('The HTML file has no tables'); 
return;
}
sheet.clear();
// Put the HTML tables to the Spreadsheet.
values.forEach(e => {
var resource = { requests: [{ pasteData: { html: true, data: e, coordinate: { sheetId: sheetId, rowIndex: rowIndex } } }] };
Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
rowIndex = sheet.getLastRow();  
})
}