使用ImportRange的GSuite模板新建表格后重复授权的解决方法

Ah, this is such a frustrating quirk with Google Sheets templates and IMPORTRANGE—I’ve helped a few teams work through this exact issue! The core problem here is that IMPORTRANGE’s authorization is tied directly to the specific file. Your template (SheetA) has its own authorization link to SheetB, but when you create a new file (SheetC) from the template, it’s a brand-new, independent file that doesn’t inherit that existing authorization.

Here are three solid solutions to avoid that manual authorization step every time:

1. Adjust SheetB’s Sharing Permissions (No-Code Fix) #

If everyone using the template is part of your Google Workspace (formerly GSuite) organization, this is the quickest win:

• Open SheetB, click the "Share" button in the top-right corner
• Set the sharing permission to "Anyone in [Your Organization] can view/edit" (pick the level that fits your security needs)
• Save the changes. Now, any new SheetC created from your template will automatically inherit access to SheetB through your org’s permissions, so IMPORTRANGE won’t ask for manual authorization anymore.

Note: This only works if you’re okay with all members of your org having access to SheetB. If you need tighter access controls, skip to the next method.

2. Replace IMPORTRANGE with Google Apps Script #

Using a simple script to pull data from SheetB avoids the file-specific authorization of IMPORTRANGE entirely. Here’s how to set it up in your template:

1. Open your template SheetA, go to Extensions > Apps Script
2. Delete the default code and paste in something like this (customize the IDs and ranges to match your setup):

function pullDataFromSheetB() {
  // Replace with your SheetB's unique ID (found in its URL)
  const sourceSheetId = "YOUR_SHEET_B_ID";
  // Replace with the range you want to import (e.g., "Data!A1:Z500")
  const sourceDataRange = "YOUR_SOURCE_RANGE";
  // Replace with the starting cell in SheetA where data should go (e.g., "A1")
  const targetStartCell = "A1";

  // Fetch data from SheetB
  const sourceSpreadsheet = SpreadsheetApp.openById(sourceSheetId);
  const rawData = sourceSpreadsheet.getRange(sourceDataRange).getValues();

  // Paste data into the current sheet
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  targetSheet.getRange(targetStartCell)
    .offset(0, 0, rawData.length, rawData[0].length)
    .setValues(rawData);
}

// Add a custom menu to make refreshing easy
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Data Tools')
    .addItem('Refresh Data from SheetB', 'pullDataFromSheetB')
    .addToUi();
}

3. Save the script, go back to SheetA, and run pullDataFromSheetB once to grant the initial authorization (you only need to do this in the template, not in copied files)
4. When someone creates a new SheetC from the template, they just click the new "Data Tools" menu and select "Refresh Data from SheetB". If they already have access to SheetB, the script will pull data without asking for extra authorization.

Bonus: You can even set up a time-driven trigger to auto-refresh data on a schedule—no manual clicks needed!

3. Template-Bound Authorization (For Restricted Teams) #

If you’re only sharing the template with a specific group of users who already have access to SheetB, you can tweak the script to run automatically when the file opens. Just add this function alongside the ones above:

function onOpen(e) {
  // Run data pull automatically on open (optional)
  pullDataFromSheetB();
  // Keep the custom menu if you want manual refresh options
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Data Tools')
    .addItem('Refresh Data from SheetB', 'pullDataFromSheetB')
    .addToUi();
}

The first time someone opens SheetC, they’ll need to allow the script to run, but that’s a one-time prompt per user—way smoother than authorizing IMPORTRANGE every time.

内容的提问来源于stack exchange，提问作者Gethin Liddell