ConcatenateTextAndExportJson

ConcatenateTextAndExportJson.js

function ConcatenateText() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Define sheet names
  const overviewSheet = ss.getSheetByName("Overview");
  const prependSheet = ss.getSheetByName("Prepend");
  const datasetsSheet = ss.getSheetByName("Datasets");
  const appendSheet = ss.getSheetByName("Append");
  const exportsSheet = ss.getSheetByName("Exports");

  // Get data from sheets
  const prependData = prependSheet.getDataRange().getValues();
  const datasetsData = datasetsSheet.getDataRange().getValues();
  const appendData = appendSheet.getDataRange().getValues();
/**
 * Matches keys in a given sheet and returns corresponding values efficiently.
 * @param {string[]} keys - Array of keys to match
 * @param {any[][]} sheetData - 2D array of sheet data
 * @returns {string[]} - Array of matched values in order of first occurrence
 */
const matchKeys = (keys, sheetData) => {
  // Create a Map for fast lookup of trimmed keys (O(m) complexity)
  const keyLookup = new Set(
    keys.map(key => key.trim().toString())
  );

  const result = [];
  
  // Iterate through sheet data only once (O(n) complexity)
  for (let i = 1; i < sheetData.length; i++) {
    const rowKey = sheetData[i][0].toString().trim();
    if (keyLookup.has(rowKey)) {
      result.push(sheetData[i][1].toString());
      // Remove found key to prevent duplicates if needed
      keyLookup.delete(rowKey);
    }
    
    // Early exit if all keys are found
    if (keyLookup.size === 0) break;
  }

  return result;
};
  /**
   * Extracts text between double curly braces `{{}}` and replaces it with matched values from datasets.
   * @param {string} inputText - Input text containing placeholders.
   * @returns {string} - Text with placeholders replaced by matched values.
   */
  const extractTextBetweenBraces = (inputText) => {
    const regex = /{{(.*?)}}/g; // Regex to match `{{placeholder}}`
    let match;
    while ((match = regex.exec(inputText)) !== null) {
      const placeholder = match[1]; // Extract the key inside `{{}}`
      const replacement = matchKeys([placeholder], datasetsData).join('\n'); // Replace with matched value
      inputText = inputText.replace(match[0], replacement); // Update the input text
    }
    return inputText;
  };

  // Get data from the Overview sheet
  const overviewData = overviewSheet.getDataRange().getValues();
  const futureJSON = []; // Array to store JSON data for export

  // Loop through each row in the Overview sheet (starting from row 3 to skip header)
  for (let i = 2; i < overviewData.length; i++) {
    const row = overviewData[i];

    // Get values from columns N, O, and P
    const questionType = row[12] ? row[12].toString() : ""; // Column N (index 13)
    const questionMetric = row[16] ? `{${row[16]}}` : "{}"; // Column Q (index 16)
    const prependKeys = row[13] ? row[13].toString().split(",") : []; // Column N (index 13)
    const injectKey = row[14] ? row[14].toString() : ""; // Column O (index 14)
    const appendKeys = row[15] ? row[15].toString().split(",") : []; // Column P (index 15)

    // Get prepended text
    const prependedText = matchKeys(prependKeys, prependData).join('\n'); // Match keys in Prepend sheet

    // Get injected text
    const injectedText = extractTextBetweenBraces(injectKey); // Replace placeholders with matched values

    // Get appended text
    const appendedText = matchKeys(appendKeys, appendData).join('\n'); // Match keys in Append sheet

    // Concatenate all parts
    const finalText = `${prependedText}\n${injectedText}\n${appendedText}`.trim(); // Combine all parts

    // Write the result to column R (index 18) in the Overview sheet
    overviewSheet.getRange(i + 1, 18).setValue(finalText);

    // Add data to futureJSON array
    futureJSON.push({
      question: finalText,
      type: questionType,
      metric: questionMetric,
    });
  }

  // Export JSON data to the Exports sheet
  exportsSheet.getRange(1, 1).setValue(JSON.stringify(futureJSON, null, 2)); // Pretty-print JSON

  SpreadsheetApp.flush(); // Ensure changes are saved
}