Retrieving Data from Excel Sheet using Google Apps Script and JavaScript

If you’re working with Google Sheets, you know how easy it can be to retrieve data using Google Apps Script. But what happens when you need to retrieve data from an Excel sheet? In this article, we’ll walk you through the process of retrieving data from an Excel sheet using Google Apps Script and JavaScript.

Step 1: Upload the Excel Sheet

The first step in retrieving data from an Excel sheet is to upload the file to Google Drive. This will allow you to access the file using Google Apps Script. To upload the Excel sheet, follow these steps:

1. Go to Google Drive and log in to your Google account.
2. Click the “New” button and select “File Upload”.
3. Select the Excel file from your computer and click “Open”.
4. Wait for the file to upload to Google Drive.

Step 2: Create Google Apps Script

Once the Excel file is uploaded to Google Drive, we need to create a Google Apps Script to retrieve the data from the sheet. To create a new script, follow these steps:

1. Open Google Drive and locate the Excel file you uploaded.
2. Right-click the file and select “Open with” > “Google Sheets”.
3. In Google Sheets, click “Tools” > “Script editor”.
4. In the new script editor window, enter the following code:

function getDataFromSheet() {
    var file = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = file.getSheetByName("Sheet1");
    var range = sheet.getDataRange();
    var values = range.getValues();
    Logger.log(values);
}

This code will retrieve data from the “Sheet1” of your Excel file and log it to the console.

Step 3: Run the Script

Now that the script is created, we can run it to retrieve the data from the Excel sheet. To run the script, follow these steps:

1. In the script editor window, click the “Run” button.
2. If prompted, review and accept the necessary permissions.
3. Open the “View” menu and select “Logs” to see the output of the script.

You should now see the data from the Excel sheet logged in the console.

Step 4: Modify the Script

Now that you have retrieved the data from the Excel sheet, you can modify the script to suit your needs. For example, you can use the data to populate a Google Sheet or display it on a web page.

To modify the script, you can change the range of cells selected in the sheet or modify the way the data is output. For example, you could change the code to output the data as a JSON object instead of logging it to the console:

function getDataFromSheet() {
    var file = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = file.getSheetByName("Sheet1");
    var range = sheet.getDataRange();
    var values = range.getValues();
    var data = {};
    for (var i = 0; i < values.length; i++) {
        var row = values[i];
        data[row[0]] = row[1];
    }
    return JSON.stringify(data);
}

The code above will return a JSON object containing the data from the Excel sheet, where the first column is the key and the second column is the value.