/** * 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; } };
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter