我有一个 Google 工作表想要导出为 CSV 文件。但工作表中有两列我不想导出。
例如图中column,我不想导出列“N”和“P”
这是我为导出编写的 Apps 脚本代码
function menu() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('Menu');
var item = menu.addItem('PICC', 'picc');
var item2 = menu.addItem('Export to CSV', 'csv');
item.addToUi();
item2.addToUi()
};
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{name: "Download Primary Time File", functionName: "saveAsCSV"}];
//ss.addMenu("Creating a Timetable", csvMenuEntries);
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('Menu');
var item = menu.addItem('PICC', 'picc');
var item2 = menu.addItem('Export to CSV', 'csv');
item.addToUi();
item2.addToUi()
};
function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("sheet1");
// create a folder from the name of the spreadsheet
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
// append ".csv" extension to the sheet name
fileName = sheet.getName() + ".csv";
// convert all available sheet data to csv format
var csvFile = convertRangeToCsvFile_(fileName, sheet);
// create a file in the Docs List with the given name and the csv data
var file = folder.createFile(fileName, csvFile);
//File downlaod
var downloadURL = file.getDownloadUrl().slice(0, -8);
showurl(downloadURL);
}
function showurl(downloadURL) {
var app = UiApp.createApplication().setHeight('60').setWidth('150');
//Change what the popup says here
app.setTitle("Your timetable CSV is ready!");
var panel = app.createPopupPanel()
//Change what the download button says here
var link = app.createAnchor('Click here to download', downloadURL);
panel.add(link);
app.add(panel);
var doc = SpreadsheetApp.getActive();
doc.show(app);
}
function convertRangeToCsvFile_(csvFileName, sheet) {
// get available data range in the spreadsheet
var activeRange = sheet.getDataRange();
try {
var data = activeRange.getValues();
var csvFile = undefined;
// loop through the data in the range and build a string with the csv data
if (data.length > 1) {
var csv = "";
for (var row = 1; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
if (data[row][col].toString().indexOf(",") != -1) {
data[row][col] = "\"" + data[row][col] + "\"";
}
}
// join each row's columns
// add a carriage return to end of each row, except for the last one
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}
catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
如您所见,我使用 for 循环导出行和列,如何进行更改以使这两列不显示在导出 CSV 中
这个修改怎么样?
修改要点:
- Modify
convertRangeToCsvFile_()
.
- From
data
检索者getValues()
,它删除列“N”和“P”。
为了反映这一点,请进行如下修改。
From :
var data = activeRange.getValues();
var csvFile = undefined;
To :
var data = activeRange.getValues();
data = data.map(function(e){return e.filter(function(_, i){return i != 13 && i != 15})}); // Added
var csvFile = undefined;
如果我误解了你的问题,请告诉我。我想修改它。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)