Bridging the Gap: How to Get Data from an Excel File (.xlsx) into Google Sheets Automatically

The Problem with IMPORTRANGE

Many of us use Google Sheets for tracking, Especially if you are at a Google School and use Google Drive as your main file storage/sharing solution. But sometimes our data starts in an Excel file (.xlsx) uploaded to Google Drive. Many of us don’t even realise that they are still using a Excel file as Google Drive and Sheets do an amazing job at presenting it as if its a Google Sheets file. But there ARE differences!
One such difference is the automation I recently needed.

Let’ say you tried the standard =IMPORTRANGE function, but it gave you an annoying “#REF! You don’t have permissions” error, even when the file was shared. Even though I have access to both files. I might even be the owner or an editor for both the source and target files. But still Im seeing the annoying error message.

If they were both Google Sheets types,

i woudl be greeted with the following prompt:

However as my source file is NOT a Google Sheets format file All I see is this lovely message:

The reason? The simple IMPORTRANGE function cannot connect to a non-native Sheets file, like Excel(.XLSX) even if it’s on your Google Drive.

The Solution: Google Apps Script
(The Automatic “File Copier”)

Now the easy solution would be to simply convert this file into a Google sheets and get on with our everyday lives and use =importrange successfully. However, And yes I do recognise this is a niche use-case, I can’t do this. The source files is shared and has been for a long time, many user are actively maintaining it and thus Converting to a NEW Google sheets format file is out of the question!

We will use a simple, custom script called Google Apps Script (a free tool built into Sheets) to work around this limitation.

This script performs three actions:

1. Temporarily Converts the Excel file into a Google Sheet.
2. Copies the data you need from that temporary sheet.
3. Deletes the temporary sheet, leaving your Drive clean.

Best of all, you can set it to run automatically at the click of a button!

Step-by-Step Guide for non-technical users and Teachers

Step 1: Prepare Your Google Sheet

1. Open the Google Sheet where you want the Excel data to appear (your Destination Sheet).
2. Go to the sheet tab where you want the imported data. In our example, we are using the tab named KS1.
3. If you haven’t create a destination sheet yet Do so now and remember the name

Step 2: Open the Apps Script Editor

1. In your Destination Sheet, click Extensions in the top menu.
2. Click Apps Script.

A new browser tab will open with the script editor.

Step 3: Enable the Advanced Drive Service (A Crucial Step!)

To allow the script to handle file conversions, you must enable a specific Google service.

1. In the script editor’s left menu, click the Services icon (it may look like a diamond or four dots).
2. Click the + icon next to Services.
3. In the “Add a service” window:
   • Select Drive API.
   • Ensure the Identifier field is set to Drive (with a capital ‘D’).
   • Click Add.

Once here select and add the Drive API

Step 4: Paste and Customize the Code

1. In the script editor, click on the file named Code.gs.
2. Delete any existing text and paste the following complete code block into the editor.

Important: You must customise a few lines(Especially the first four) to match your files.

// --- CONFIGURATION --- This needs to be customised by you following the instructions on sethideclercq.com
// IMPORTANT IN AppsScript(Here) Select services and Add the DRIVE API Service!

const EXCEL_FILE_ID = "FileID"; // The ID you would usually use in IMPORTRANGE (This is the document ID in your source file URL)
const EXCEL_SHEET_NAME = "ExampleComputing";                      // The name of the sheet/tab in the Excel file (XLSX)
const DESTINATION_SHEET_NAME = "ExampleDestination";                      // The sheet in *this* target file where you want the data
const RANGE_TO_IMPORT = "ExampleRangeA1:AR";                           // The range of data to import (For example; A1:Z)

// ---------------------

function importExcelData() {
  const excelFile = DriveApp.getFileById(EXCEL_FILE_ID);

  // 1. TEMPORARILY CONVERT the Excel file to a Google Sheet (this is the trick)
  const tempSheet = Drive.Files.insert({title: "TEMP_IMPORT_SHEET", mimeType: MimeType.GOOGLE_SHEETS}, excelFile.getBlob(), {convert: true});
  const tempSheetId = tempSheet.getId();
  
  // 2. OPEN the converted file to access the data
  const sourceSpreadsheet = SpreadsheetApp.openById(tempSheetId);
  const sourceSheet = sourceSpreadsheet.getSheetByName(EXCEL_SHEET_NAME);
  
  if (!sourceSheet) {
    SpreadsheetApp.getUi().alert(`Sheet '${EXCEL_SHEET_NAME}' not found in the Excel source file.`);
    // Clean up temporary file
    Drive.Files.remove(tempSheetId);
    return;
  }
  
  // 3. READ the data
  const data = sourceSheet.getRange(RANGE_TO_IMPORT).getValues();
  
  // 4. WRITE the data to the destination sheet
  const destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(DESTINATION_SHEET_NAME);
  
  // Clear existing content and set new data
  destinationSheet.clearContents();
  destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  
  // 5. CLEAN UP the temporary Google Sheet file
  Drive.Files.remove(tempSheetId);
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Excel data imported successfully from your source file!', 'Import Complete', 5);
}

// Add a custom menu for easy access
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Excel Import')
      .addItem('Import KS1 Computing Data Now', 'importExcelData') //Customise this with the name for your function. Example here is Import KS1 Computing Data. 
      .addToUi();
}

If you would like to see this file in AppScript with the comments you can create your won copy here: link

Step 5: Run and Authorize the Script

1. Click the Save icon (diskette icon) in the Apps Script editor.
2. Go back to your Google Sheet and refresh the browser page.
3. You will now see a new menu item called “Excel Data Tools.” or Whatever you decided to Name you menu.
4. Click Excel Data Tools > 📥 Import Data Now. (This will again, depend on what you’ve named it in your code)
5. The Authorization Screen: The first time, Google will ask for permission. Click Review Permissions, select your account, and click Allow. (You may need to click ‘Advanced’ first).

The data should now appear in your destination sheet!

Step 6: Set up Automatic Refresh (Trigger)

You don’t want to click that menu every time. Let’s make it run on a schedule!

1. In the Apps Script editor, click the Triggers icon (it looks like a clock) on the left menu.
2. Click the + Add Trigger button at the bottom right.
3. Configure the trigger settings as follows:
   • Choose which function to run: importExcelData
   • Choose which deployment to run: Head
   • Select event source: Time-driven
   • Select type of time-based trigger: Choose your preferred frequency (e.g., Hour timer, Day timer).
   • Select time of day: Choose a time (e.g., Every 6 hours, or 8am to 9am for a daily refresh).
4. Click Save.

Your data connection is now automated!

If you found this helpful or have anyone who would benefit from this workaround Share the article.