Google Spread Sheet Export CSV in the same folder

Question

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;

}

Tanaike Tanaike · Accepted Answer · 2020-03-20T01:43:21

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

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

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.

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

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

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Google Spread Sheet Export CSV in the same folder

1 Answers

Modification point:

Modified script:

References: