CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsexport-to-csv
Jean
Jean

Reputation: 83

Google Spread Sheet Export CSV in the same folder

I am using the script below, working very well (thanks to Ted Bell). But I need to adapt it because I need the CSV file saved in the same folder as the spreadsheet. Could you please help me with this matter? The code below creates a new folder each time on My Drive. The CSV is ok regarding its name and its format: with semicolon delimiter.

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{
    name: "export as csv file",
    functionName: "saveAsCSV"
}];
ss.addMenu("CSV Export", csvMenuEntries);
var a1 = ss.getRange("A1").getValue();
var name = "MyCompanyName_"+a1;
ss.rename(name);
};

function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssname = ss.getName();
var sheet = ss.getActiveSheet();
var sheetname = sheet.getSheetName();
//Logger.log("DEBUG: the name of the spreadsheet is "+ssname);//DEBUG
//Logger.log("DEBUG: the sheet name is "+sheetname);// DEBUG

//// create a folder from the name of the spreadsheet
var folder = DriveApp.createFolder(ssname.toLowerCase() + '_' + 
sheetname.toLowerCase().replace(/ /g, '_') + '_csv_' + new Date().getTime());
//Logger.log("DEBUG: the folder name is "+folder);//DEBUG

// append ".csv" extension to the sheet name
var fileName = ssname + '_' + sheetname + ".csv";

// convert all available sheet data to csv format
var csvFile = so_4225484202(fileName);

// 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 isValidDate(date) {
return date && Object.prototype.toString.call(date) === "[object Date]" && !isNaN(date);
}


function so_4225484202(filename) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var paramsheet = ss.getSheetByName("Parameters");

var linearray = [];
var rowdata = [];
var csv = "";
var fieldvalue = "";

var param = paramsheet.getRange(2, 2, 2);
var paramValues = param.getValues();
//Logger.log("DEBUG: parameters = "+param.getA1Notation());//DEBUG
var fieldDelimiter = paramValues[0][0];
var textDelimiter = paramValues[1][0];
//Logger.log("DEBUG: field delimiter: "+fieldDelimiter+", text delim: 
"+textDelimiter);//DEBUG
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
//Logger.log("DEBUG: lastColumn: "+lastColumn+", lastRow: "+lastRow);//DEBUG

// Get array of values in the Data Range
var rangeValues = rangeData.getValues();

// Loop through array and build values for csv
for (i = 0; i < lastRow; i++) {
    for (j = 0; j < lastColumn; j++) {

        var value = rangeValues[i][j];
        var theType = typeof value;

        if (theType === "object") {
            var testdate = isValidDate(value);
            //Logger.log("if typeof is object: testdate: "+testdate);//DEBUG
            var testtype = typeof testdate;
            if (testtype === "boolean") {
                // variable is a boolean
                //Logger.log("Its a date");//DEBUG
                theType = "date";
            } else {
                //Logger.log("Its not a date");//DEBUG
            }
        }
        if (theType === "string") {
            value = textDelimiter + value + textDelimiter;
        }

        rowdata.push([value]);
    };

    //Logger.log("DEBUG: rowdata: "+rowdata);//DEBUG
    csv += rowdata.join(fieldDelimiter) + "\n";
    var rowdata = [];

};

//Logger.log("DEBUG: csv: "+csv);//DEBUG
return csv;

}

Upvotes: 1

Views: 337

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Here is my understanding:

  • You want to create the CSV file in the same folder of the active Spreadsheet.
  • You want to achieve this by modifying your script.

Modification point:

  • In order to retrieve the folder of the active Spreadsheet, DriveApp.getFileById(ss.getId()).getParents().next() is used for retrieving the folder.

Modified script:

Please modify the function of saveAsCSV() in your script as follows.

From:

var folder = DriveApp.createFolder(ssname.toLowerCase() + '_' + sheetname.toLowerCase().replace(/ /g, '_') + '_csv_' + new Date().getTime());

To:

var folder = DriveApp.getFileById(ss.getId()).getParents().next();

References:

Upvotes: 2

Related Questions