convert_SheetsTable_To_MarkdownTable_With_PII_Redaction.js

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('View more cell actions')
    .addItem('Convert table to Markdown', 'convertTableToMarkdown')
    .addToUi();
}

function convertTableToMarkdown() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  const columnNames = sheet.getRange(1, 1, 1, numCols).getValues()[0].map(String);
  const piiColumns = ['Email_pii', 'Address_pii']; // Replace with your desired column names for array matching

  let markdownTable = '';

  for (let i = 0; i < numRows; i++) {
    for (let j = 0; j < numCols; j++) {
      const columnName = columnNames[j];
      const isPiiColumn = piiColumns.includes(columnName);

      if (isPiiColumn) {
        values[i][j] = '***';
      }

      markdownTable += `|${values[i][j]}`;
    }

    markdownTable += '|\n';

    if (i === 0) {
      const horizontalLine = Array.from({ length: numCols }).fill('---').join('|');
      markdownTable += `${horizontalLine}|\n`;
    }
  }

  Logger.log(markdownTable);

  // Show the Markdown table in a dialog box
  const htmlOutput = HtmlService.createHtmlOutput(`<pre>${markdownTable}</pre>`);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Markdown Table');
}

function convertTableToMarkdown() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  const columnNames = sheet.getRange(1, 1, 1, numCols).getValues()[0].map(String);
  const piiColumns = ['Email_pii', 'Adress_pii']; // Replace with your desired column names for array matching

  let markdownTable = '';

  for (let i = 0; i < numRows; i++) {
    for (let j = 0; j < numCols; j++) {
      const columnName = columnNames[j];
      const isPiiColumn = piiColumns.includes(columnName);

      if (isPiiColumn) {
        values[i][j] = '***';
      }

      markdownTable += `|${values[i][j]}`;
    }

    markdownTable += '|\n';

    if (i === 0) {
      const horizontalLine = Array.from({ length: numCols }).fill('---').join('|');
      markdownTable += `${horizontalLine}|\n`;
    }
  }

  Logger.log(markdownTable);
}