Convert CSV, XLS or XLSX file in Drive to 2D array
Thu Feb 03 2022 17:21:26 GMT+0000 (Coordinated Universal Time)
Saved by
@MakiProductions
/**
* Convert CSV or XLSX file to 2D array
* @param {object} file The Drive file
* @param {string} folderId The folder to save to
* @return {array}
*/
const convertFileDataToArray = (file, folderId) => {
try {
const blob = file.getBlob();
const contentType = blob.getContentType();
if (contentType.includes('text/csv')) { // If file is csv
const csv = file.getBlob().getDataAsString('UTF-16');
const delimiter = csv.indexOf("\t") != -1 ? "\t" : ","; // parse comma separated values
return Utilities.parseCsv(csv, delimiter); // return array
} else if (contentType.includes('application/vnd')) { // If file is xlsx
const newSheetId = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: folderId }] }, file.getId()).getId();
SpreadsheetApp.openById(newSheetId).rename('temp_' + new Date().getTime());
const sheet = SpreadsheetApp.openById(newSheetId).getSheets()[0]; // Get first tab of spreadsheet
const data = sheet.getDataRange().getValues(); // get array values
DriveApp.getFileById(newSheetId).setTrashed(true); // delete file
return data;
}
} catch (e) {
log(e);
return null;
}
};
content_copyCOPY
Comments