CODEWITHSUNDEEP
csvgoogle-sheetsgoogle-sheets-api
Sven
Sven

Reputation: 21

Google sheets Export CSV file for specific range

Good afternoon,

I tried to extract a table into csv file via script code. On the internet I can only find how to extract the full sheets but I would like create a csv picking up just a certain range "B8:K500".

The code creates a CSV file but with only one cell and saying 'undefined'. Nothing else. Not figures from the table I wanted to create.

In convertRangeToCsvFile_(csvFileName, sheet) something seems to be wrong. I am new to coding. Just tried to apply what I found online.

function ExportCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var range = ss.getSheets()[0].getRange("B8:K500");
   SpreadsheetApp.setActiveRange(range);

  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
  for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // 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
    folder.createFile(fileName, csvFile);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}


function convertRangeToCsvFile_(csvFileName, sheet) {  
   // get available data range in the spreadsheet
  var range = sheet.getRange("B8:K500"); 
  var values = SpreadsheetApp.getActiveSheet().getRange("B8:K500").getValues();

  var csvFile = values;
  DriveApp.createFile("mycsv.csv", csvFile);

  }

Upvotes: 2

Views: 3731

Answers (1)

Quaris
Quaris

Reputation: 359

The .getValues function you're using here...

var values = SpreadsheetApp.getActiveSheet().getRange("B8:K500").getValues();

...will return an array within an array, with the inner arrays being rows and outer array being the columns like so:

[["A1","B1","C1"],["A2","B2","C2"],["A3","B3","C3"]]

Where "A1", "B1" and so on would be whatever values are in cells A1, B1...

To get rid of the brackets and replace them with commas and semicolons/new lines/whatever other delimiter you're using, you could use some sort of for loop.

There are multiple methods. Although this probably isn't the most efficient way, I might do this:

var row=0
var col=0
var csvFile=""
for (row=0; row<values.length; row++) {
  for (col=0; col<values[0].length; col++) {
    //insert comma just before the values if it's not the first value
    if (col != 0) {
      csvFile+=","
    }
    //get value at column and row and add it to csvFile
    csvFile+=values[row][col]
  }
  //insert semicolons (or whatever you want) between whole rows
  csvFile+=";"
}

Upvotes: 1

Related Questions