Dev-Dipesh · December 1, 2024 05:13
diff --git a/code.gs b/code.gs
 // This is the main code that processes the raw data from the 'Raw Data' sheet and places it in 'Formatted Data' sheet.
 function processRawData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = sheet.getSheetByName('Raw Data'); // Raw data arrives here from Zapier
  var formattedSheet = sheet.getSheetByName('Formatted Data'); // Formatted data is posted here after processing

  // Get the data from the Raw Data sheet
  var rawData = rawSheet.getDataRange().getValues();

  for (var i = 1; i < rawData.length; i++) { // Start from row 1 to skip headers
    var row = rawData[i];
    if (row[2] === "Processed") continue; // Skip already processed rows (assuming column C)

    var processedRow = [];

    // Extract fields
    var rawDate = row[0]; // Assuming Date is in the first column
    var emailBody = row[1]; // Assuming Email Body is in the second column
    
    // Process Date
    var formattedDate = ""; // Default empty
    try {
      var dateMatch = rawDate.match(/\b(\d{1,2}\s\w+\s\d{4})\b/);
      if (dateMatch) {
        var dateObj = new Date(dateMatch[0]); // Extract and parse the date
        formattedDate = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "dd/MM/yy");
      }
    } catch (err) {
      formattedDate = "Invalid Date"; // Fallback for invalid dates
    }

    // Extract Restaurant Name
    var restaurantName = emailBody.match(/from (.+)/)?.[1]?.trim() || "Unknown";

    // Extract Order Items
    var orderStart = emailBody.indexOf("Delivered") + "Delivered".length;
    var orderEnd = emailBody.indexOf("Total paid -");
    var orderText = emailBody.substring(orderStart, orderEnd).trim();
    var orderItems = orderText.split("\n").filter(item => item.includes("X") && /\d/.test(item)); // Filter valid items

    // Extract Total Paid
    var totalPaidMatch = emailBody.match(/Total paid -\s*₹([\d.,]+)/);
    var totalPaid = totalPaidMatch ? parseFloat(totalPaidMatch[1].replace(/,/g, '')).toFixed(2) : "0.00";

    // Skip rows with price 0
    if (parseFloat(totalPaid) === 0) {
      rawSheet.getRange(i + 1, 3).setValue("Skipped: Price 0"); // Mark as skipped
      continue;
    }

    // Append processed data
    processedRow.push(formattedDate, restaurantName, orderItems.join(", "), totalPaid);

    // Append to Formatted Data sheet
    formattedSheet.appendRow(processedRow);

    // Mark as processed in Raw Data
    rawSheet.getRange(i + 1, 3).setValue("Processed"); // Add "Processed" in column C
  }
 }
diff --git a/customMenu.gs b/customMenu.gs
 // This adds a custom drop-down menu in Google Sheets to run the scripts manually if required.
 function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Utilities') // Creates a new menu called "Utilities"
    .addItem('Remove Duplicates', 'removeDuplicates') // Adds an option to call removeDuplicates
    .addItem('Process Raw Data', 'processRawData') // Optional: Adds an option to run processRawData
    .addToUi(); // Adds the menu to the UI
 }
diff --git a/dedupe.gs b/dedupe.gs
 // This code removes the duplicate entries from the formatted data
 function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formatted Data');
  var data = sheet.getDataRange().getValues(); // Get all data
  var uniqueData = [];
  var seenKeys = new Set();

  // Iterate through the rows
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var key = row.join("||"); // Create a unique key by combining all columns

    // Add the row to uniqueData only if it's not already seen
    if (!seenKeys.has(key)) {
      uniqueData.push(row);
      seenKeys.add(key);
    }
  }

  // Clear the sheet and re-insert unique rows
  sheet.clearContents();
  sheet.getRange(1, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
 }
diff --git a/insights.md b/insights.md
diff --git a/post.md b/post.md
	// This is the main code that processes the raw data from the 'Raw Data' sheet and places it in 'Formatted Data' sheet.
	function processRawData() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet();
	var rawSheet = sheet.getSheetByName('Raw Data'); // Raw data arrives here from Zapier
	var formattedSheet = sheet.getSheetByName('Formatted Data'); // Formatted data is posted here after processing

	// Get the data from the Raw Data sheet
	var rawData = rawSheet.getDataRange().getValues();

	for (var i = 1; i < rawData.length; i++) { // Start from row 1 to skip headers
	var row = rawData[i];
	if (row[2] === "Processed") continue; // Skip already processed rows (assuming column C)

	var processedRow = [];

	// Extract fields
	var rawDate = row[0]; // Assuming Date is in the first column
	var emailBody = row[1]; // Assuming Email Body is in the second column

	// Process Date
	var formattedDate = ""; // Default empty
	try {
	var dateMatch = rawDate.match(/\b(\d{1,2}\s\w+\s\d{4})\b/);
	if (dateMatch) {
	var dateObj = new Date(dateMatch[0]); // Extract and parse the date
	formattedDate = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "dd/MM/yy");
	}
	} catch (err) {
	formattedDate = "Invalid Date"; // Fallback for invalid dates
	}

	// Extract Restaurant Name
	var restaurantName = emailBody.match(/from (.+)/)?.[1]?.trim() \|\| "Unknown";

	// Extract Order Items
	var orderStart = emailBody.indexOf("Delivered") + "Delivered".length;
	var orderEnd = emailBody.indexOf("Total paid -");
	var orderText = emailBody.substring(orderStart, orderEnd).trim();
	var orderItems = orderText.split("\n").filter(item => item.includes("X") && /\d/.test(item)); // Filter valid items

	// Extract Total Paid
	var totalPaidMatch = emailBody.match(/Total paid -\s*₹([\d.,]+)/);
	var totalPaid = totalPaidMatch ? parseFloat(totalPaidMatch[1].replace(/,/g, '')).toFixed(2) : "0.00";

	// Skip rows with price 0
	if (parseFloat(totalPaid) === 0) {
	rawSheet.getRange(i + 1, 3).setValue("Skipped: Price 0"); // Mark as skipped
	continue;
	}

	// Append processed data
	processedRow.push(formattedDate, restaurantName, orderItems.join(", "), totalPaid);

	// Append to Formatted Data sheet
	formattedSheet.appendRow(processedRow);

	// Mark as processed in Raw Data
	rawSheet.getRange(i + 1, 3).setValue("Processed"); // Add "Processed" in column C
	}
	}
	// This adds a custom drop-down menu in Google Sheets to run the scripts manually if required.
	function onOpen() {
	var ui = SpreadsheetApp.getUi();
	ui.createMenu('Utilities') // Creates a new menu called "Utilities"
	.addItem('Remove Duplicates', 'removeDuplicates') // Adds an option to call removeDuplicates
	.addItem('Process Raw Data', 'processRawData') // Optional: Adds an option to run processRawData
	.addToUi(); // Adds the menu to the UI
	}
	// This code removes the duplicate entries from the formatted data
	function removeDuplicates() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formatted Data');
	var data = sheet.getDataRange().getValues(); // Get all data
	var uniqueData = [];
	var seenKeys = new Set();

	// Iterate through the rows
	for (var i = 0; i < data.length; i++) {
	var row = data[i];
	var key = row.join("\|\|"); // Create a unique key by combining all columns

	// Add the row to uniqueData only if it's not already seen
	if (!seenKeys.has(key)) {
	uniqueData.push(row);
	seenKeys.add(key);
	}
	}

	// Clear the sheet and re-insert unique rows
	sheet.clearContents();
	sheet.getRange(1, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
	}