如何通过Google App Script为Google Sheets内无直链图片添加跳转链接

Got it, let's fix this—since you can't use HYPERLINK with IMAGE due to no direct image links, using Apps Script's "Assign script" feature is the perfect workaround. Here's how to do it, both manually for single images and in bulk for multiple ones:

Option 1: Manual Setup (For 1-2 Images) #

1. Insert your image into the sheet: Go to Insert > Image > Image over cells (or "Image in cell" if you prefer, but "over cells" is easier to click).
2. Assign a script to the image: Right-click the image, select Assign script, then type a custom function name (e.g., openMyLink) and hit Enter.
3. Write the script:
   • Open the Apps Script editor via Extensions > Apps Script.
   • Replace the default code with this function (swap the URL with your target):

     function openMyLink() {
       const targetUrl = "https://www.google.com"; // Your desired link here
       // Use HTML to open the link in a new tab (direct window.open doesn't work in Apps Script)
       const htmlOutput = HtmlService.createHtmlOutput(`
         <script>
           window.open('${targetUrl}', '_blank');
           google.script.host.close();
         </script>
       `);
       SpreadsheetApp.getUi().showModalDialog(htmlOutput, '');
     }
     

4. Test it: Go back to your sheet and click the image—it should open your link in a new browser tab.

Option 2: Bulk Setup (For Multiple Images) #

If you have lots of images, this script will assign clickable links to all images in your active sheet (or adjust to target specific images):

1. Open the Apps Script editor (Extensions > Apps Script).
2. Paste this code:

   // Assigns a clickable link to all images in the active sheet
   function assignLinksToAllImages() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const allImages = sheet.getImages();
     const defaultUrl = "https://www.google.com"; // Replace with your link
   
     allImages.forEach(image => {
       // Assign the script function with your target URL
       image.assignScript(`openLink("${defaultUrl}")`);
     });
   }
   
   // Handles opening the link when the image is clicked
   function openLink(url) {
     const htmlOutput = HtmlService.createHtmlOutput(`
       <script>
         window.open('${url}', '_blank');
         google.script.host.close();
       </script>
     `);
     SpreadsheetApp.getUi().showModalDialog(htmlOutput, '');
   }
   

3. Customize (optional): If you want different images to link to different URLs, modify the script to pull links from adjacent cells. For example, if each image's anchor cell has the link in the cell to its right:

   function assignCustomLinksToImages() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const allImages = sheet.getImages();
   
     allImages.forEach(image => {
       const anchorCell = image.getAnchorCell();
       if (anchorCell) {
         // Get URL from the cell to the right of the image's anchor cell
         const targetUrl = anchorCell.offset(0, 1).getValue();
         if (targetUrl) {
           image.assignScript(`openLink("${targetUrl}")`);
         }
       }
     });
   }
   

4. Run the script: Click the run button (▶️) next to the function name (e.g., assignLinksToAllImages), and follow the authorization prompts (you'll need to allow the script to access your spreadsheet and show dialogs).

Important Notes #

• Authorization: The first time you run the script, Google will warn you it's an "unverified app"—this is normal for custom scripts. Just click "Advanced" > "Go to [Script Name]" to proceed.
• New Tab Behavior: We use HtmlService because Apps Script can't directly open browser tabs; the modal dialog's JavaScript handles this and closes itself immediately.
• Function Names: When using "Assign script", make sure the function name matches exactly (it's case-sensitive!).

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