How to Connect Data from Excel Sheet to Google Sheet Using Google Apps Script

by

Connecting data from an Excel sheet to a Google Sheet can streamline your workflow, especially if you are transitioning from Microsoft Excel to Google Sheets. Google Apps Script provides a powerful way to automate this process. In this blog post, I’ll guide you through the steps to connect data from an Excel sheet to a Google Sheet using Google Apps Script.

Prerequisites

Before we start, ensure you have:

  1. A Google account.
  2. Access to Google Drive and Google Sheets.
  3. A basic understanding of Google Apps Script.

Step-by-Step Guide

Step 1: Prepare Your Excel File

First, you need to have your Excel file ready. If you don’t have one, you can create a simple Excel file with some sample data.

  1. Save the Excel file to your local computer.
  2. Open Google Drive and upload your Excel file.

Step 2: Convert Excel File to Google Sheet

To work with Google Apps Script, you need to convert the Excel file to a Google Sheet.

  1. Right-click on the uploaded Excel file in Google Drive.
  2. Select Open with > Google Sheets.
  3. This will create a new Google Sheet with the same data.

Step 3: Create a New Google Sheet for Data Transfer

  1. Create a new Google Sheet where you want to transfer the data.
  2. Note the Google Sheet ID from the URL. It looks something like this: https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit.

Step 4: Create Google Apps Script

  1. Open the newly created Google Sheet.
  2. Click on Extensions > Apps Script.
  3. Delete any default code in the script editor and add the following code:
function importExcelData() {
  // ID of the source Google Sheet (converted from Excel)
  var sourceSheetId = 'SOURCE_SHEET_ID';
  
  // ID of the target Google Sheet where data will be transferred
  var targetSheetId = 'TARGET_SHEET_ID';
  
  // Open the source and target Google Sheets
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSheetId);
  var targetSpreadsheet = SpreadsheetApp.openById(targetSheetId);
  
  // Get the first sheet from both source and target
  var sourceSheet = sourceSpreadsheet.getSheets()[0];
  var targetSheet = targetSpreadsheet.getSheets()[0];
  
  // Get the range of data from the source sheet
  var sourceRange = sourceSheet.getDataRange();
  
  // Get the values from the source range
  var sourceValues = sourceRange.getValues();
  
  // Clear the target sheet
  targetSheet.clear();
  
  // Set the values in the target sheet
  targetSheet.getRange(1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  
  Logger.log('Data imported successfully from source to target sheet');
}

Step 5: Explanation

  1. Source and Target Sheet IDs:
    • Replace 'SOURCE_SHEET_ID' with the ID of the Google Sheet that was converted from the Excel file.
    • Replace 'TARGET_SHEET_ID' with the ID of the target Google Sheet.
  2. Opening Spreadsheets:
    • SpreadsheetApp.openById(sourceSheetId) and SpreadsheetApp.openById(targetSheetId) open the source and target spreadsheets, respectively.
  3. Getting Sheets:
    • getSheets()[0] gets the first sheet in both the source and target spreadsheets.
  4. Getting Data Range and Values:
    • getDataRange() gets the range of all data in the source sheet.
    • getValues() gets all the values within the range.
  5. Clearing and Setting Values:
    • clear() clears any existing data in the target sheet.
    • getRange(1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues) sets the values in the target sheet, starting from cell A1.

Step 6: Running the Script

  1. Save your script by clicking on File > Save.
  2. Click on the Run button (the triangle icon) to execute the importExcelData function.
  3. You might need to authorize the script to access your Google Sheets. Follow the on-screen prompts to grant the necessary permissions.

Step 7: Testing

Open the target Google Sheet to see if the data from the source Google Sheet (originally the Excel file) has been transferred successfully. You can check the Apps Script logs for confirmation by going to View > Logs.

Conclusion

By following these steps, you can seamlessly connect data from an Excel sheet to a Google Sheet using Google Apps Script. This method is efficient and helps automate the data transfer process, ensuring your data is always up-to-date in Google Sheets.