Modify Code - Convert File To CSV. Google Apps Script / Google sheets

Question

The below code does as intended - except it ONLY copies the first 3 columns - and I need it to copy all the Data on the spreadsheet.I thought when the code says sheet.getDataRange(); that means all data but not in this case. Could I get a brief explanation of the issue rather than just the answer - love to know why 3 columns only are being selected.

function STCSV() {
var ss = SpreadsheetApp.openById("");
var sheets = ss.getSheets();
var sheet = sheets[3];
var formattedDate = Utilities.formatDate(new Date(), "GMT+10", "dd-MM-yyyy' '");
// append ".csv" extension to the sheet name
fileName = formattedDate + SpreadsheetApp.getActiveSpreadsheet().getName() + " ST CSV IMPORT "  + 
".csv";

var folder = DriveApp.getFolderById("");

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

function convertRangeToCsvFile_(csvFileName, sheet) {
// get available data range in the spreadsheet
var activeRange = sheet.getDataRange();
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 = 0; 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;
}

Appreciate any help. Thanks

I have to apologize for my poor English skill. In order to correctly understand about your question, can you provide the sample Spreadsheet and sample output you expect? Of course, please remove your personal information. — Tanaike
VERY sorry all. I made a rookie error and didnt notice sheet 4 only had 3 columns !!! I updated array to correct sheet and solved the issue. — Tom Sawkins

Unknown Unknown · Accepted Answer · 2020-03-04T11:03:59

The getDataRange() method returns a range corresponding to the dimensions in which data is present; therefore, the sheet.getDataRange() does return all the data from the sheet.

The reason only 3 columns are selected is because your sheet actually has only 3 columns.

Reference

Sheet Class Apps Script - getDataRange().

Modify Code - Convert File To CSV. Google Apps Script / Google sheets

1 Answers