Convert CSV, XLS or XLSX file in Drive to 2D array

PHOTO EMBED

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