无需Import Range函数表格间数据导入：Google Apps Script报错排查

Let's break down why you're hitting that "Bad value" error and fix it step by step:

The Root Cause #

Your line 6 is the direct culprit:

var targetSpreadsheet = SpreadsheetApp.openById("Control Panel");

The openById() method requires a unique spreadsheet ID (a long string of letters, numbers, and hyphens), not the friendly name of your spreadsheet ("Control Panel"). Passing the name instead of the valid ID is exactly what's triggering the "Bad value" error.

How to Grab the Correct Spreadsheet ID #

1. Open your "Control Panel" spreadsheet in your browser
2. Look at the URL bar — it’ll look like this: https://docs.google.com/spreadsheets/d/abc123XYZ789-12abC/edit#gid=0
3. The string between /d/ and /edit (in this example, abc123XYZ789-12abC) is your spreadsheet ID. Copy that exact string.

Fixed Script (Plus a Hidden Pitfall Fix) #

I also fixed a second easy-to-miss issue: when using setValues(), the target range must match the size of your source data. Your source range is A2:A100 (99 rows × 1 column), so the target range needs to be B3:B101 (not just B3, which would throw a range mismatch error later). Here's the polished code:

function logMemberId () {
  // Get the source "Database" sheet from your active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database"); 
  // Replace with your actual Control Panel spreadsheet ID
  var targetSpreadsheet = SpreadsheetApp.openById("YOUR_CONTROL_PANEL_ID_HERE"); 
  // Pull values from A2:A100 (99 rows of data)
  var rangeOfNumbers = ss.getRange("A2:A100").getValues(); 
  // Paste values into the matching target range (B3 to B101)
  targetSpreadsheet.getSheetByName("YOUR_TARGET_SHEET_NAME").getRange("B3:B101").setValues(rangeOfNumbers); 
}

Pro tip: Don’t forget to replace YOUR_CONTROL_PANEL_ID_HERE with the ID you copied, and YOUR_TARGET_SHEET_NAME with the exact name of the sheet inside your Control Panel spreadsheet where you want the customer IDs to go (it’s case-sensitive!)

Quick Pre-Run Checks #

• Double-check the spreadsheet ID has no extra spaces or missing characters
• Make sure your script has permission to access both spreadsheets (you’ll get a prompt on first run)
• Confirm the source sheet ("Database") and target sheet names are spelled correctly

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