如何用变量数据填充Google Sheets Script的HTML侧边栏？

Hey there! Let's walk through how to pass data from two different spreadsheets to your Google Sheets sidebar, display it for users, and add appointment functionality. Since you know vanilla JavaScript but haven't touched jQuery yet, we'll stick to plain JS for the frontend—no need to jump into jQuery right now.

First, we'll write functions to pull data from both spreadsheets and pass it to the sidebar template. Here's how:

// Get data from both spreadsheets and format it into a reusable object
function getSpreadsheetData() {
  // Replace with your first spreadsheet ID and sheet name
  const firstSheet = SpreadsheetApp.openById('YOUR_FIRST_SPREADSHEET_ID').getSheetByName('DataSheet');
  // Adjust the range to match where your data lives
  const firstSheetData = firstSheet.getRange('A2:B15').getValues();

  // Replace with your second spreadsheet ID and sheet name (for appointments)
  const appointmentSheet = SpreadsheetApp.openById('YOUR_SECOND_SPREADSHEET_ID').getSheetByName('Appointments');
  const appointmentData = appointmentSheet.getRange('A2:C15').getValues();

  // Return data as an object so we can easily access it in the sidebar
  return {
    sourceData: firstSheetData,
    existingAppointments: appointmentData
  };
}

// Load the sidebar and pass the fetched data to it
function showAppointmentSidebar() {
  const template = HtmlService.createTemplateFromFile('AppointmentSidebar');
  // Attach our data to the template variable
  template.sidebarData = getSpreadsheetData();
  const htmlOutput = template.evaluate().setTitle('Appointment Manager');
  SpreadsheetApp.getUi().showSidebar(htmlOutput);
}

Create a new HTML file in your script project, then use vanilla JS to render the data and add an appointment form. We'll use the template variable we passed from the .gs file to populate the content:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      /* Quick styling to make the sidebar look clean */
      .sidebar-container { padding: 1rem; }
      .data-block { margin: 1.5rem 0; padding: 1rem; border: 1px solid #e0e0e0; border-radius: 4px; }
      .form-group { margin: 0.8rem 0; }
      label { display: block; margin-bottom: 0.3rem; font-weight: 500; }
      input { width: 100%; padding: 0.5rem; border: 1px solid #ddd; border-radius: 4px; }
      button { background-color: #1a73e8; color: white; border: none; padding: 0.6rem 1.2rem; border-radius: 4px; cursor: pointer; }
      button:hover { background-color: #1557b0; }
    </style>
  </head>
  <body>
    <div class="sidebar-container">
      <h3>Appointment Manager</h3>

      <!-- Display data from first spreadsheet -->
      <div class="data-block">
        <h4>Source Data</h4>
        <div id="sourceDataContainer"></div>
      </div>

      <!-- Display existing appointments from second spreadsheet -->
      <div class="data-block">
        <h4>Existing Appointments</h4>
        <div id="appointmentContainer"></div>
      </div>

      <!-- Form to create new appointments -->
      <div class="data-block">
        <h4>Book New Appointment</h4>
        <div class="form-group">
          <label>Client Name:</label>
          <input type="text" id="clientName">
        </div>
        <div class="form-group">
          <label>Date:</label>
          <input type="date" id="apptDate">
        </div>
        <div class="form-group">
          <label>Time:</label>
          <input type="time" id="apptTime">
        </div>
        <button onclick="saveNewAppointment()">Save Appointment</button>
      </div>
    </div>

    <script>
      // Pull the data passed from the .gs template
      const sidebarData = <?= JSON.stringify(sidebarData) ?>;

      // Render source data from first spreadsheet
      function renderSourceData() {
        const container = document.getElementById('sourceDataContainer');
        let html = '<ul>';
        sidebarData.sourceData.forEach(row => {
          html += `<li>${row[0]}: ${row[1]}</li>`;
        });
        html += '</ul>';
        container.innerHTML = html;
      }

      // Render existing appointments
      function renderAppointments() {
        const container = document.getElementById('appointmentContainer');
        let html = '<ul>';
        sidebarData.existingAppointments.forEach(row => {
          html += `<li>${row[0]} - ${row[1]} at ${row[2]}</li>`;
        });
        html += '</ul>';
        container.innerHTML = html;
      }

      // Save new appointment to the second spreadsheet
      function saveNewAppointment() {
        const name = document.getElementById('clientName').value;
        const date = document.getElementById('apptDate').value;
        const time = document.getElementById('apptTime').value;

        // Call the .gs function to save data, then refresh the appointment list
        google.script.run.withSuccessHandler(() => {
          alert('Appointment saved successfully!');
          // Fetch updated data and re-render the appointment list
          google.script.run.withSuccessHandler(updatedData => {
            sidebarData.existingAppointments = updatedData.existingAppointments;
            renderAppointments();
            // Clear form fields
            document.getElementById('clientName').value = '';
            document.getElementById('apptDate').value = '';
            document.getElementById('apptTime').value = '';
          }).getSpreadsheetData();
        }).saveAppointmentToSheet(name, date, time);
      }

      // Render data when the sidebar loads
      window.onload = () => {
        renderSourceData();
        renderAppointments();
      };
    </script>
  </body>
</html>

Finally, add a function to write new appointments to your second spreadsheet:

function saveAppointmentToSheet(clientName, apptDate, apptTime) {
  const appointmentSheet = SpreadsheetApp.openById('YOUR_SECOND_SPREADSHEET_ID').getSheetByName('Appointments');
  // Append the new appointment as a new row
  appointmentSheet.appendRow([clientName, apptDate, apptTime]);
}

Quick Notes to Get Started: #

• Replace all instances of YOUR_FIRST_SPREADSHEET_ID and YOUR_SECOND_SPREADSHEET_ID with the actual IDs from your spreadsheet URLs (the long string between /d/ and /edit).
• When you first run showAppointmentSidebar(), you'll need to authorize the script to access your spreadsheets.
• Adjust the range values (like A2:B15) to match where your data is stored in each sheet.

内容的提问来源于stack exchange，提问作者Rob Campbell