Parsing data out of an Excel file from within a Google Sheets script

Question

TL;DR: I am looking to find out if it's possible via a Google Sheets spreadsheet to programmatically open an Excel document stored in the same location and parse out data for processing.

MORE INFO: The data in the Excel file is in a very inconvenient state but it is an export I have no control over. I'd just like to be able to drag that Excel file into my Google Drive and press a button in my Google Sheets spreadsheet that will go through that Excel file and pull out the bits I need. I'll need to be able to iterate through each of the Excel worksheets (consistently named but no set number) for data that is in a predictable format in each sheet. I'll be doing some processing of that data against data already present in my Google Sheets spreadsheet.

I'm not concerned about the learning involved but I can't find evidence that what I want to do is even possible. This thread has a solution involving Excel which I can adapt to if I have to, but I'm hoping for a solution that doesn't use VBA.

Answer 1

• You want to retrieve the values from a XLSX file.
• You want to put the retrieved values to Google Spreadsheet.
• There are the XLSX file and Google Spreadsheet in the same folder on Google Drive.
• You want to know whether above situation can be achieved by Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

In order to achieve your goal, I would like to propose the following flow.

1. Retrieve the XLSX file and convert it to Google Spreadsheet.
   • By this, the values can be directly retrieved by Google Apps Script.
   • As the sample script, you can see these threads.
     • Script to convert .XLSX to Google Sheet and move converted file
     • What is the most efficient way to convert a .XLS to Google Sheets in App Script?
2. Retrieve the values from the converted Google Spreadsheet.
   • At this time, you can also select the retrieving values.
   • In this case, for example, you can use the method of getValues.
3. Put the retrieved values to the existing Google Spreadsheet.
   • In this case, for example, you can use the method of setValues.

Note:

• From your question, I'm not sure about your actual XLSX file. So I think that it is required to test above flow using your XLSX file and Google Spreadsheet. So if above flow cannot be used for your situation, I apologize.

References:

• getValues()
• setValues()

If I misunderstood your question and this was not the direction you want, I apologize.