请求实现Google Sheets匿名用户点击图标跨表复制A2并添加时间戳

Since Google Sheets' built-in script triggers don’t work for anonymous users, we’ll use a Google Apps Script Web App to handle this task. This approach lets anyone (even unauthenticated users) click the icon and trigger the copy/timestamp action. Here’s how to set it up:

Step 1: Build the Web App Script #

First, we need a script that creates new spreadsheets and copies the data. Here’s what to do:

• Open your source Google Sheet (the one with cell C2 where you want the icon).
• Go to Extensions > Apps Script to open the script editor.
• Replace the default Code.gs content with this code:

function doGet(e) {
  try {
    // Pull source sheet details from the request URL
    const sourceSheetId = e.parameter.sourceId;
    const targetRange = e.parameter.range || 'A2';
    
    // Fetch the value from your source sheet (make sure it's publicly accessible!)
    const sourceSheet = SpreadsheetApp.openById(sourceSheetId).getActiveSheet();
    const copiedValue = sourceSheet.getRange(targetRange).getValue();
    
    // Create a new spreadsheet with a timestamped name
    const newSheetName = `Copied Data - ${new Date().toLocaleString()}`;
    const newSpreadsheet = SpreadsheetApp.create(newSheetName);
    const destinationSheet = newSpreadsheet.getActiveSheet();
    
    // Add headers, timestamp, and the copied value
    destinationSheet.getRange('A1').setValue('Timestamp');
    destinationSheet.getRange('B1').setValue('Copied Value');
    destinationSheet.getRange('A2').setValue(new Date());
    destinationSheet.getRange('B2').setValue(copiedValue);
    
    // Format the timestamp for readability
    destinationSheet.getRange('A2').setNumberFormat('yyyy-MM-dd HH:mm:ss');
    
    // Return a success message with the new sheet's URL
    return ContentService.createTextOutput(`Success! New spreadsheet: ${newSpreadsheet.getUrl()}`);
  } catch (error) {
    return ContentService.createTextOutput(`Oops, something went wrong: ${error.message}`);
  }
}

This script listens for web requests, fetches the value from A2, creates a new spreadsheet, and populates it with the timestamp and copied data.

Step 2: Deploy the Web App for Anonymous Access #

Next, we need to make this script accessible to everyone:

• In the script editor, click Deploy > New deployment.
• Click the gear icon next to "Type" and select Web app.
• Configure these settings:
  • Execute as: Select Me (your email) (this gives the script permission to create spreadsheets under your account).
  • Who has access: Choose Anyone, even anonymous (this is critical for allowing unauthenticated users to trigger the action).
• Click Deploy, then go through the authorization prompts (you may need to select "Advanced" > "Go to [Script Name]" to grant permissions).
• Copy the generated Web App URL—you’ll need this for the icon link.

Step 3: Add the Clickable Icon to C2 #

Now, let’s link the icon to our Web App:

• Back in your source sheet, go to Insert > Image > Image over cells and pick an image for your clickable icon. Position it over cell C2.
• Right-click the image and select Link.
• Paste your Web App URL, then add these parameters to the end:
  ?sourceId=YOUR_SOURCE_SPREADSHEET_ID&range=A2
  • Replace YOUR_SOURCE_SPREADSHEET_ID with the ID from your source sheet’s URL (it’s the long string between /d/ and /edit).
• Click Apply to save the link.

Step 4: Make Your Source Sheet Publicly Accessible #

For the Web App to read the value from A2, your source sheet needs to be visible to anyone:

• Click Share in the top-right corner of your source sheet.
• Under General access, select Anyone with the link and set the permission to Viewer (this is enough for the script to read the cell value).

Test It Out #

To verify it works for anonymous users:

• Log out of your Google account.
• Open your source sheet’s URL.
• Click the icon in C2. You should see a success message with a link to the new spreadsheet, which contains the timestamp and the value from A2.

Quick Notes #

• Every click creates a new spreadsheet. If you’d prefer to add rows to a single destination sheet instead, just let me know—I can adjust the script!
• All new spreadsheets will be owned by you (the person who deployed the Web App). You can modify the script to share them with specific users if needed.
• If you redeploy the Web App later, you’ll need to update the link on your icon to use the new URL.

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