Read Excel file from Google Shared Drive in App Script without "insert"

Question

I've managed to read an Excel file from a Google Shared Drive and convert it to a Google sheet with the code below, based on the answers in this post.

  var files = DriveApp.getFolderById("1AUPqh_WjYasdfasdfkiWThXxC").getFilesByName(fileName);
  var excelFile = (files.hasNext()) ? files.next() : null;
  var blob = excelFile.getBlob();
  var config = {
    title: "[Converted] " + excelFile.getName(),
    parents: [{id: DriveApp.getRootFolder().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  var convertedSpreadsheet = Drive.Files.insert(config, blob,{supportsAllDrives: true});

  var sourceWsData = SpreadsheetApp.openById(convertedSpreadsheet.id).getSheetByName("Export").getDataRange().getValues();

This creates a Google sheet file under "My Drive" (or a Shared Drive, if I specify that location). However, I only need the data from the Excel file in order to use it further down in the script (via sourceWsData), without "saving" the Google sheet anywhere.

Currently, "My Drive" gets cluttered since there is a Google sheet created every time I run the script. Does anyone have an idea, if that is possible?

Answer 1

I believe your goal is as follows.

• You want to retrieve the values from an XLSX file without creating a file using Google Apps Script.

In this case, how about the following sample script?

In this sample script, in order to directly retrieve the values from XLSX data, I used SheetJS of the Javascript library. Because, in the current stage, there are no built-in methods in Google Apps Script for directly retrieving the values from the XLSX data. And, in your showing script, the files except for the XLSX file are also retrieved. So, in this case, I used the search query.

Usage:

1. Install SheetJS library.

Please copy the script of the SheetJS library from https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js, and paste the script to the script editor of Google Spreadsheet, and save the script.

In this case, I would like to recommend the following flow.

1. Add a new script to the script editor. For example, the filename is SheetJS.
2. Copy and paste the script of https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js to the added script file, and save the script.
3. Copy and paste the following sample script to the other script file (It's the default script file (Code.gs)).

2. Prepare custom function.

Please copy and paste the following script to the script editor (this is the same Spreadsheet installed SheetJS library.) and set the variables and save the script.

function myFunction_SheetJS() {
  const filename = "###"; // Please set the filename of XLSX
  const folderId = "###"; // Please set the folder ID.
  const sheetName = "Sheet1"; // Please set the sheet name you want to retrieve the values.

  // Retrieve XLSX file using the filename.
  const files = DriveApp.getFolderById(folderId).searchFiles(`title='${filename}' and mimeType='${MimeType.MICROSOFT_EXCEL}' and trashed=false`);
  const file = files.hasNext() && files.next();
  if (!file) throw new Error("No file.");
  const fileId = file.getId();

  // Retrieve values from XLSX data.
  const data = new Uint8Array(DriveApp.getFileById(fileId).getBlob().getBytes());
  const book = XLSX.read(data, { type: "array" });
  const csv = XLSX.utils.sheet_to_csv(book.Sheets[sheetName]);
  const values = Utilities.parseCsv(csv);

  console.log(values);
}

• When this script is run, the values are directly retrieved from "Sheet1" in the XLSX file of filename using the SheetJS library without creating a temporal Spreadsheet file.

Note:

• In this script, if you don't want to copy and paste the SheetJS library to the script editor, you can also install it with the following script. But, in this case, the process cost becomes high. Please be careful about this.

  const cdnjs = "https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText());
  

References:

• searchFiles(params) of Class Folder
• SheetJS CE
• fetch(url)
• Directly Retrieving Values from XLSX data using SheetJS with Google Apps Script (Author: me)