如何通过选定的列将Google表格导出为CSV [英] How to export google sheet as CSV by selected columns

问题描述

我有一个要导出为CSV文件的Google表格.但是工作表中有2列我不想导出. 例如，在图片列中，我不想导出列"N "和"P" 这是我为导出而编写的Apps脚本代码

I have a Google sheet want to export as CSV file. But there are 2 columns in the sheet I don't want to export. For example, in the picturecolumn, I don't want to export column "N" and "P" Here are the Apps Script code I wrote for export

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

如您所见，我用于循环以导出行和列，如何进行更改以使两列不显示在导出CSV中

As you can see, I used for loop to export the rows and columns, how can I make change to let the two columns not showing in the export CSV

推荐答案

此修改如何?

• 修改convertRangeToCsvFile_().
  • 从getValues()检索到的data中，删除"N"和"P"列.
  • Modify convertRangeToCsvFile_().
    • From data retrieved by getValues(), it removes the columns "N" and "P".

    为了反映这一点，请进行如下修改.

    In order to reflect this, please modify as follows.

    var data = activeRange.getValues();
    var csvFile = undefined;
    

    收件人:

    var data = activeRange.getValues();
    data = data.map(function(e){return e.filter(function(_, i){return i != 13 && i != 15})}); // Added
    var csvFile = undefined;
    

    如果我误解了您的问题，请告诉我.我想修改它.

    If I misunderstand your question, please tell me. I would like to modify it.

    这篇关于如何通过选定的列将Google表格导出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！