I have:
- .xlsx files being automatically imported on a daily basis to a google drive folder
I want:
- each file converted to a CSV and saved to a different folder
- each file converted to a Google Sheet and saved to a different folder
- the original xlsx file deleted once done processing
Currently my process is this:
- Convert xlsx to 2 new files: CSV & Google Sheet
- Save CSVs to CSV Folder
- Save Google Sheets to Sheets folder
I was originally using this https://ctrlq.org/code/20248-convert-excel-to-csv tutorial to convert to CSV until I realized it saved an "Untitled" copy of each .xlsx sheet as Drive spreadsheet to my root folder. I could not figure out how to assign a title or folder location to those untitled. Being able to do that would also fix my immediate problem.
Then I attempted using a modified version of this (below) https://ctrlq.org/code/20500-convert-microsoft-excel-xlsx-to-google-spreadsheet with the MimeType .CSV which correctly placed my CSV's in the right folder with the right name, but the data wasn't parsed correctly and looked corrupted.
function exceltoSheets() {
var SOURCE_XLS_FOLDER = '123465';
var SHEET_FOLDER = '789456';
var CSV_TEST = '456123';
var sourceFolderID = DriveApp.getFolderById(SOURCE_XLS_FOLDER),
mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for (var m = 0; m < mimes.length; m++) {
var sourceFiles = sourceFolderID.getFilesByType(mimes[m]);
while (sourceFiles.hasNext()) {
try {
var sourceFile = sourceFiles.next();
var sourceName = sourceFile.getName().replace(".xlsx","");
var sourceNameTC = sourceName + ".csv"
var sourceNameDS = "ds_data_import_" + sourceName;
var fileId = sourceFile.getId();
var blob = sourceFile.getBlob();
var resourceDS = {
title: sourceNameDS,
mimeType: MimeType.GOOGLE_SHEETS,
convert: true,
parents: [{id: SHEET_FOLDER}]
};
var resourceTC = {
title: sourceNameTC,
mimeType: MimeType.CSV,
convert: true,
parents: [{id: CSV_TEST}],
};
Drive.Files.insert(resourceDS, blob);
Drive.Files.insert(resourceTC, blob);
} catch (f) {
Logger.log(f.toString());
}
sourceFile.setTrashed(true);
}
}
}
If I parse the CSVs correctly I end up with Untitled sheets in my root folder, if I parse the Sheets correctly I end up with corrupted CSVs. I want the result:
- xlsx converted to CSV in designated folder
- xlsx converted to Google Sheet in designated folder
- xlsx deleted off drive once processing complete
I need
might be different fromAll I want is
. How about this? – Tanaikexlsx data aggregated to last line in sheet file
andall data in each file copied after row 12 and saved to the last line in a designated drive spreadsheet
. From your script and question, I cannot understand about the process after the values are retrieved from the XLSX file. Can I ask you about this? I apologize for my poor English skill. – Tanaike