pleabargain · October 29, 2024 07:43
diff --git a/code.gs b/code.gs
 // Code.gs
 function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Custom Menu')
    .addItem('Show Form', 'showSidebar')
    .addToUi();
 }

 function showSidebar() {
  const html = HtmlService.createHtmlOutput(getHtml())
    .setTitle('My Data')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
 }

 // Function to generate UUID
 function generateUUID() {
  const timestamp = new Date().getTime();
  return `ID-${timestamp}-${Math.floor(Math.random() * 1000000)}`;
 }

 function getHtml() {
  return `
    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
        <style>
          body { font-family: Arial, sans-serif; padding: 10px; }
          .form-group { margin-bottom: 15px; }
          label { display: block; margin-bottom: 5px; }
          input { width: 100%; padding: 5px; margin-bottom: 10px; }
          button { 
            background-color: #4CAF50;
            color: white;
            padding: 10px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            width: 100%;
          }
          button:hover { background-color: #45a049; }
          .disabled { 
            background-color: #cccccc;
            cursor: not-allowed;
          }
        </style>
      </head>
      <body>
        <form onsubmit="handleSubmit(this); return false;">
          <div class="form-group">
            <label for="name">Name:</label>
            <input type="text" id="name" name="name" required>
          </div>
          <div class="form-group">
            <label for="dob">DOB:</label>
            <input type="date" id="dob" name="dob" required>
          </div>
          <div class="form-group">
            <label for="email">Email:</label>
            <input type="email" id="email" name="email" required>
          </div>
          <button type="submit" id="submitBtn">Submit</button>
        </form>

        <script>
          function handleSubmit(form) {
            const submitBtn = document.getElementById('submitBtn');
            submitBtn.textContent = 'Submitting...';
            submitBtn.disabled = true;
            submitBtn.classList.add('disabled');

            const formData = {
              name: form.name.value,
              dob: form.dob.value,
              email: form.email.value
            };
            
            google.script.run
              .withSuccessHandler(function() {
                onSuccess(form);
                submitBtn.textContent = 'Submit';
                submitBtn.disabled = false;
                submitBtn.classList.remove('disabled');
              })
              .withFailureHandler(function(error) {
                onFailure(error);
                submitBtn.textContent = 'Submit';
                submitBtn.disabled = false;
                submitBtn.classList.remove('disabled');
              })
              .saveFormData(formData);
          }

          function onSuccess(form) {
            alert('Data submitted successfully!');
            form.reset();
          }

          function onFailure(error) {
            alert('Error: ' + error.message);
          }
        </script>
      </body>
    </html>
  `;
 }

 function saveFormData(formData) {
  // Get the active spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get or create the my_data sheet
  let sheet = ss.getSheetByName('my_data');
  if (!sheet) {
    sheet = ss.insertSheet('my_data');
    
    // Add headers in the first row with correct order
    sheet.getRange(1, 1, 1, 4).setValues([[
      'UUID',
      'Name',
      'DOB',
      'Email'
    ]]);
    
    // Format headers
    sheet.getRange(1, 1, 1, 4)
      .setBackground('#f3f3f3')
      .setFontWeight('bold')
      .setWrap(true);
    
    // Freeze the header row
    sheet.setFrozenRows(1);
    
    // Set column widths
    sheet.setColumnWidth(1, 200); // UUID
    sheet.setColumnWidth(2, 150); // Name
    sheet.setColumnWidth(3, 100); // DOB
    sheet.setColumnWidth(4, 200); // Email
  }
  
  // Generate UUID
  const uuid = generateUUID();
  
  // Get the last row with content
  const lastRow = Math.max(sheet.getLastRow(), 1);
  
  // Add new row of data in the correct order: UUID, Name, DOB, Email
  sheet.getRange(lastRow + 1, 1, 1, 4).setValues([[
    uuid,
    formData.name,
    formData.dob,
    formData.email
  ]]);
  
  return true;
 }
	// Code.gs
	function onOpen() {
	SpreadsheetApp.getUi()
	.createMenu('Custom Menu')
	.addItem('Show Form', 'showSidebar')
	.addToUi();
	}

	function showSidebar() {
	const html = HtmlService.createHtmlOutput(getHtml())
	.setTitle('My Data')
	.setWidth(300);
	SpreadsheetApp.getUi().showSidebar(html);
	}

	// Function to generate UUID
	function generateUUID() {
	const timestamp = new Date().getTime();
	return `ID-${timestamp}-${Math.floor(Math.random() * 1000000)}`;
	}

	function getHtml() {
	return `
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	<style>
	body { font-family: Arial, sans-serif; padding: 10px; }
	.form-group { margin-bottom: 15px; }
	label { display: block; margin-bottom: 5px; }
	input { width: 100%; padding: 5px; margin-bottom: 10px; }
	button {
	background-color: #4CAF50;
	color: white;
	padding: 10px 15px;
	border: none;
	border-radius: 4px;
	cursor: pointer;
	width: 100%;
	}
	button:hover { background-color: #45a049; }
	.disabled {
	background-color: #cccccc;
	cursor: not-allowed;
	}
	</style>
	</head>
	<body>
	<form onsubmit="handleSubmit(this); return false;">
	<div class="form-group">
	<label for="name">Name:</label>
	<input type="text" id="name" name="name" required>
	</div>
	<div class="form-group">
	<label for="dob">DOB:</label>
	<input type="date" id="dob" name="dob" required>
	</div>
	<div class="form-group">
	<label for="email">Email:</label>
	<input type="email" id="email" name="email" required>
	</div>
	<button type="submit" id="submitBtn">Submit</button>
	</form>

	<script>
	function handleSubmit(form) {
	const submitBtn = document.getElementById('submitBtn');
	submitBtn.textContent = 'Submitting...';
	submitBtn.disabled = true;
	submitBtn.classList.add('disabled');

	const formData = {
	name: form.name.value,
	dob: form.dob.value,
	email: form.email.value
	};

	google.script.run
	.withSuccessHandler(function() {
	onSuccess(form);
	submitBtn.textContent = 'Submit';
	submitBtn.disabled = false;
	submitBtn.classList.remove('disabled');
	})
	.withFailureHandler(function(error) {
	onFailure(error);
	submitBtn.textContent = 'Submit';
	submitBtn.disabled = false;
	submitBtn.classList.remove('disabled');
	})
	.saveFormData(formData);
	}

	function onSuccess(form) {
	alert('Data submitted successfully!');
	form.reset();
	}

	function onFailure(error) {
	alert('Error: ' + error.message);
	}
	</script>
	</body>
	</html>
	`;
	}

	function saveFormData(formData) {
	// Get the active spreadsheet
	const ss = SpreadsheetApp.getActiveSpreadsheet();

	// Get or create the my_data sheet
	let sheet = ss.getSheetByName('my_data');
	if (!sheet) {
	sheet = ss.insertSheet('my_data');

	// Add headers in the first row with correct order
	sheet.getRange(1, 1, 1, 4).setValues([[
	'UUID',
	'Name',
	'DOB',
	'Email'
	]]);

	// Format headers
	sheet.getRange(1, 1, 1, 4)
	.setBackground('#f3f3f3')
	.setFontWeight('bold')
	.setWrap(true);

	// Freeze the header row
	sheet.setFrozenRows(1);

	// Set column widths
	sheet.setColumnWidth(1, 200); // UUID
	sheet.setColumnWidth(2, 150); // Name
	sheet.setColumnWidth(3, 100); // DOB
	sheet.setColumnWidth(4, 200); // Email
	}

	// Generate UUID
	const uuid = generateUUID();

	// Get the last row with content
	const lastRow = Math.max(sheet.getLastRow(), 1);

	// Add new row of data in the correct order: UUID, Name, DOB, Email
	sheet.getRange(lastRow + 1, 1, 1, 4).setValues([[
	uuid,
	formData.name,
	formData.dob,
	formData.email
	]]);

	return true;
	}