使用Tanaike的单引号拼接Google Script：复制粘贴多引号问题求解

I’ve run into this exact issue before—here’s what’s happening and how to fix it quickly:

Root Cause #

Google Sheets uses a leading single quote (') as a special text qualifier to mark content as plain text (instead of a formula). When your script writes a string that starts with a single quote (like 'your content'), Sheets automatically adds an extra leading single quote internally to escape it. This lets the cell display your intended text (with one leading quote), but the underlying stored value has two leading quotes. When you copy the cell, you’re grabbing that stored value instead of the displayed one—hence the extra quotes.

Solutions #

Here are three reliable fixes to stop the extra quotes from appearing:

1. Format Cells as Plain Text First

Set the target cell’s number format to plain text before writing your value. This tells Sheets not to interpret the leading single quote as a qualifier, so it stores the string exactly as you generate it.

Example code:

function writeTextWithoutExtraQuotes() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetRange = sheet.getRange("A1");
  
  // Set cell to plain text format first
  targetRange.setNumberFormat('@');
  
  // Write your single-quoted string
  const text = "'Hello World'";
  targetRange.setValue(text);
}

2. Use setRichTextValue Instead of setValue

Rich text values bypass Sheets’ automatic text qualifier handling. You can create a rich text string with your exact content and write it directly:

function writeRichText() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetRange = sheet.getRange("A1");
  
  const text = "'Hello World'";
  const richText = SpreadsheetApp.newRichTextValue().setText(text).build();
  
  targetRange.setRichTextValue(richText);
}

3. Escape Embedded Single Quotes Explicitly

If you’re building strings with embedded single quotes (like 'It's a test'), double the internal single quote to avoid unintended escaping: 'It''s a test'. This ensures Sheets stores the string exactly as you intend, without adding extra quotes.

Verification #

After applying any fix, check the formula bar when selecting the cell—you should see exactly the string you wrote (no extra leading quotes). When you copy and paste into a text editor, the content will match what’s displayed in the cell.

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