能否通过Google Sheets Apps Script实现自动打开插入菜单、选择绘图工具并启用涂鸦功能，完成签名后将其定位到指定位置？

Great question! Let's break down what's achievable and what limitations you'll run into with your requested workflow:

Core Feasibility Overview #

Your overall goal is partially achievable with native Apps Script capabilities, but some UI-specific actions can't be directly automated due to Google's sandbox and security constraints. Let's dive into each part:

1. Automatically opening the "Insert" menu #

• Not possible directly: Apps Script runs server-side and can't simulate user clicks on the Sheets UI (like opening the native Insert menu). Instead, you can create a custom menu that triggers your signature workflow—this gives users a clear, one-click way to start the process without relying on the native menu.

2. Selecting the Drawing tool + Graffiti option #

• Direct UI automation isn't possible: There's no API to programmatically switch to the graffiti (pen) tool within the Sheets drawing editor. However, you can skip the native menu entirely and use script to insert a blank drawing canvas directly. Users will still need to manually select the graffiti tool in the drawing editor, but this cuts out the menu navigation step.

3. Auto-placing the signature in a specified location #

• Fully achievable: Once the user finishes signing and closes the drawing editor, you can use Apps Script to position the drawing exactly where you want it (e.g., aligned to a specific cell). You can even pre-position the drawing canvas before the user signs, so their signature lands in the right spot immediately.

Example Workflow Using Native Drawing Tool #

Here's a script that sets up a custom menu, inserts a pre-sized drawing canvas at your target location, and prompts the user to sign:

// Adds a custom menu to the Sheets UI when the document opens
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Signature Tool')
    .addItem('Start Signature', 'createSignatureCanvas')
    .addToUi();
}

// Creates a blank drawing canvas at your specified cell
function createSignatureCanvas() {
  const sheet = SpreadsheetApp.getActiveSheet();
  // Define your target cell (adjust this to your needs)
  const targetCell = sheet.getRange("C5");
  
  // Insert a new drawing and set its size
  const signatureDrawing = sheet.insertDrawing();
  signatureDrawing.setWidth(220);
  signatureDrawing.setHeight(100);
  
  // Position the drawing at the target cell (10px offset from top/left)
  signatureDrawing.setPosition(
    targetCell.getRow(),
    targetCell.getColumn(),
    10,
    10
  );
  
  // Prompt the user to sign
  SpreadsheetApp.getUi().alert(
    "Open the drawing editor, select the pen tool, and sign. Close the editor when done—your signature will stay in place!"
  );
}

Better Alternative: HTML Canvas Sidebar (Full Automation) #

If you want to eliminate the manual step of selecting the graffiti tool, use an HTML sidebar with a canvas element. This lets users sign directly in the sidebar (with the "pen" tool active by default) and automatically inserts the signature image into your target location.

Step 1: Apps Script Code #

// Show the signature sidebar
function showSignatureSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('SignatureSidebar')
    .setTitle('Signature Capture');
  SpreadsheetApp.getUi().showSidebar(html);
}

// Insert the signature image into the target cell
function insertSignature(imageData) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const targetCell = sheet.getRange("C5");
  
  // Convert base64 image data to a blob
  const imageBlob = Utilities.newBlob(
    Utilities.base64Decode(imageData.split(',')[1]),
    'image/png'
  );
  
  // Insert the image at the target cell
  sheet.insertImage(imageBlob, targetCell.getColumn(), targetCell.getRow());
}

// Add custom menu for the sidebar tool
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Signature Tool')
    .addItem('Sidebar Signature', 'showSignatureSidebar')
    .addToUi();
}

Step 2: HTML Sidebar File (Create a new file named SignatureSidebar.html) #

<!DOCTYPE html>
<html>
  <body style="padding: 1rem;">
    <h3>Sign Here</h3>
    <canvas id="signatureCanvas" width="220" height="100" style="border: 1px solid #ccc;"></canvas>
    <div style="margin-top: 1rem;">
      <button onclick="clearCanvas()" style="margin-right: 0.5rem;">Clear</button>
      <button onclick="submitSignature()">Submit Signature</button>
    </div>

    <script>
      const canvas = document.getElementById('signatureCanvas');
      const ctx = canvas.getContext('2d');
      let isDrawing = false;

      // Set up drawing logic (pen tool active by default)
      canvas.addEventListener('mousedown', startDrawing);
      canvas.addEventListener('mousemove', draw);
      canvas.addEventListener('mouseup', stopDrawing);
      canvas.addEventListener('mouseout', stopDrawing);

      function startDrawing(e) {
        isDrawing = true;
        ctx.beginPath();
        ctx.moveTo(e.offsetX, e.offsetY);
      }

      function draw(e) {
        if (!isDrawing) return;
        ctx.lineWidth = 2;
        ctx.lineCap = 'round';
        ctx.strokeStyle = '#000';
        ctx.lineTo(e.offsetX, e.offsetY);
        ctx.stroke();
      }

      function stopDrawing() {
        isDrawing = false;
      }

      function clearCanvas() {
        ctx.clearRect(0, 0, canvas.width, canvas.height);
      }

      function submitSignature() {
        const imageData = canvas.toDataURL('image/png');
        google.script.run.insertSignature(imageData);
        alert('Signature inserted successfully!');
        clearCanvas();
      }
    </script>
  </body>
</html>

Final Notes #

• The native drawing tool approach is simpler but requires users to select the pen tool manually.
• The HTML sidebar approach offers a more seamless experience, with the "signature tool" active by default and full control over placement.

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