Google Apps Script for copying content and note of multiple cells in Google Sheets. AI-generated.

CopyWithNotes.gs

/**
 * @fileoverview A robust, self-contained Google Apps Script for copying
 * the content and comments of multiple selected cells to the clipboard
 * in a TSV (Tab-Separated Value) format. This version is built as an
 * add-on and includes a custom HTML escape function for resilience.
 */

/**
 * Creates a custom add-on menu in the spreadsheet UI when the sheet is opened.
 * Using createAddonMenu() places the menu in the "Extensions > Add-ons" section,
 * which is the recommended practice for script distribution.
 */
function onOpen() {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Copy selected cells incl. notes', 'showCopyDialog')
      .addToUi();
}

/**
 * A custom function to manually escape HTML characters in a string.
 * This is a workaround for environments where Utilities.htmlEscape is not available.
 * @param {string} str The string to escape.
 * @return {string} The escaped string.
 */
function escapeHtml(str) {
  if (typeof str !== 'string') {
    return '';
  }
  return str.replace(/&/g, '&')
            .replace(/</g, '&lt;')
            .replace(/>/g, '&gt;')
            .replace(/"/g, '&quot;')
            .replace(/'/g, '&#39;');
}

/**
 * A helper function to prepare a string for a TSV cell,
 * handling tabs and double quotes by enclosing the string
 * in double quotes and escaping internal quotes.
 * @param {string} str The string to format for TSV.
 * @return {string} The TSV-safe string.
 */
function toTsvString(str) {
  if (typeof str !== 'string') {
    return '""';
  }
  // Check if the string contains a tab, double quote, or newline.
  if (str.includes('\t') || str.includes('"') || str.includes('\n')) {
    // Replace all double quotes with two double quotes.
    const escapedStr = str.replace(/"/g, '""');
    // Enclose the entire string in double quotes.
    return `"${escapedStr}"`;
  }
  // No special characters, so return the string as is.
  return str;
}

/**
 * This function retrieves the values and comments from all selected cells,
 * formats them as a TSV, and presents them in a dialog for the user to copy.
 */
function showCopyDialog() {
  // Get the active spreadsheet and the currently selected range.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getActiveRange();
  
  // Get all the values and notes (comments) from the selected range.
  const values = range.getValues();
  const notes = range.getNotes();
  
  // Initialize the TSV content with the header row, using tabs as delimiters.
  let tsvContent = 'Cell\tcontent\tnote\n';
  
  // Iterate through each row and column in the selected range.
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[0].length; j++) {
      const cellValue = values[i][j] ? String(values[i][j]) : '';
      const cellNote = notes[i][j] ? String(notes[i][j]) : '';
      const cellAddress = range.getCell(i + 1, j + 1).getA1Notation();
      
      // Format the data for this row using tabs as the delimiter.
      const cellData = [
        toTsvString(cellAddress),
        toTsvString(cellValue),
        toTsvString(cellNote)
      ];
      
      // Add the row to the TSV string.
      tsvContent += cellData.join('\t') + '\n';
    }
  }

  // Create the HTML output for the modal dialog.
  const htmlOutput = HtmlService.createHtmlOutput(
    `<div style="font-family: 'Inter', sans-serif; padding: 16px; text-align: center;">
      <h2 style="font-weight: 600;">Copy to Clipboard (TSV)</h2>
      <p style="margin-top: 8px;">Press <strong>Cmd+A</strong> then <strong>Cmd+C</strong> to copy the TSV data below.</p>
      <textarea id="copyText" style="width: 100%; height: 160px; padding: 12px; border-radius: 8px; border: 1px solid #d1d5db; background-color: #f9fafb; font-size: 14px; resize: vertical; margin-top: 16px;">${escapeHtml(tsvContent)}</textarea>
      <script>
        document.getElementById('copyText').select();
      </script>
    </div>`
  ).setWidth(500).setHeight(350);

  // Show the dialog to the user.
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Clipboard');
}