Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

我想你幫我利用google sheet和google apps scripts “doGet”制作一個好像goolgle官方的review . 前提: 我有客戶A,B,C,…

  1. User

    我想你幫我利用google sheet和google apps scripts “doGet”制作一個好像goolgle官方的review . 前提: 我有客戶A,B,C, 我想HTML先有3個BOOSTRAP做的CARD帶有”REVIEW”按鈕,點入去就開始有個MODAL FORM. FORM 要有好像goolgle官方的review介面.

    重點留意: FORM HTML文件使用RateYo jQuery插件来显示评级星级. FORM HTML內容要有留言者名稱,評分,留言和SUBMIT按鈕,當”SUBMIT”後要回到主頁及更新. 當再次打開FORM HTML 要把已留言的記錄顯示在下方. 主頁CARD HTML要有星星顯示及總平均總分. 所有HTML要置中及有CSS 留言記錄要加入留言的日期時間.

    我已創建一個名為“Reviews”的工作表。在這個工作表中,創建了以下列Name,Email,Rating,Comment,Timestamp

    請最後只提供我GS CODE 和HTML就可以.

  2. Assistant

    根据您的要求,我提供以下的HTML和GS代码。在这里,我们用到了Bootstrap及RateYo的CDN。

    HTML部分 (請將以下HTML放入演示中的窗口):

    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/rateYo/2.3.2/jquery.rateyo.min.css">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/rateYo/2.3.2/jquery.rateyo.min.js"></script>
      </head>
      <body>
        <div class="container d-flex justify-content-center">
          <div class="text-center">
            <h1>顾客评价</h1>
            <div class="card-columns">
              <div class="card" data-id="A">
                <div class="card-body">
                  <h5 class="card-title">客户A</h5>
                  <div class="average-rating"></div>
                  <button class="btn btn-primary review">发表评价</button>
                </div>
              </div>
              <div class="card" data-id="B">
                <div class="card-body">
                  <h5 class="card-title">客户B</h5>
                  <div class="average-rating"></div>
                  <button class="btn btn-primary review">发表评价</button>
                </div>
              </div>
              <div class="card" data-id="C">
                <div class="card-body">
                  <h5 class="card-title">客户C</h5>
                  <div class="average-rating"></div>
                  <button class="btn btn-primary review">发表评价</button>
                </div>
              </div>
            </div>
          </div>
        </div>
    
        <div class="modal" tabindex="-1" role="dialog">
          <div class="modal-dialog" role="document">
            <div class="modal-content" id="form">
              <div class="modal-header">
                <h5 class="modal-title">提交评价</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="关闭">
                  <span aria-hidden="true">&times;</span>
                </button>
              </div>
              <div class="modal-body">
                <form>
                  <input type="hidden" name="customerId" id="customerId">
                  <div class="form-group">
                    <label for="name">昵称</label>
                    <input type="text" class="form-control" id="name" name="name">
                  </div>
                  <div class="form-group">
                    <label for="rating">评分</label><br>
                    <div id="rating" class="form-control"></div>
                  </div>
                  <div class="form-group">
                    <label for="comment">留言</label>
                    <textarea class="form-control" id="comment" name="comment" rows="3"></textarea>
                  </div>
                  <button type="submit" class="btn btn-primary">提交评价</button>
                </form>
              </div>
              <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">关闭</button>
              </div>
            </div>
          </div>
        </div>
    
        <script>
          updateAverages();
    
          $("#rating").rateYo({
            starWidth: "20px",
            rating: 0,
            fullStar: true
          });
    
          $(".review").on("click", function(){
            var id = $(this).closest('.card').data('id');
            $("#customerId").val(id);
            $('.modal').modal('show');
          });
    
          $("form").on("submit", function(e){
            e.preventDefault();
            var rating = $("#rating").rateYo("rating");
            google.script.run.withSuccessHandler(function() {
              updateAverages();
              $('.modal').modal('hide');
              $('form')[0].reset();
              $("#rating").rateYo("rating", 0);
            }).submitReview(this);
          });
    
          function updateAverages() {
            google.script.run.withSuccessHandler(function(averages){
              for(var id in averages) {
                $('.card[data-id="' + id + '"] .average-rating').rateYo({
                  readOnly: true,
                  rating: averages[id],
                  starWidth: '20px'
                });
              }
            }).getAverages();
          }
        </script>
      </body>
    </html>
    

    GS代码 (请将以下代码放入您的Google Apps Script编辑器中):

    function doGet() {
      return HtmlService.createHtmlOutputFromFile('index');
    }
    
    function submitReview(form) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Reviews');
      sheet.appendRow([
        form.customerId,
        form.name,
        form.rating,
        form.comment,
        new Date()
      ]);
    }
    
    function getAverages() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Reviews');
      var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();
      var averages = {};
    
      data.forEach(function(row){
        var id = row[0];
        var rating = row[2];
        if(typeof averages[id] === 'undefined') {
          averages[id] = {total: rating, count: 1};
        } else {
          averages[id].total += rating;
          averages[id].count ++;
        }
      });
    
      for(var id in averages) {
        averages[id] = averages[id].total / averages[id].count;
      }
    
      return averages;
    }
    

    要使上述代碼生效,請按照以下步驟操作:

    1. 將HTML代碼放入名為“index”的HTML文件中。
    2. 將GS代碼放入名為“Code”的.gs文件中。
    3. 在GS代碼窗口中,點擊“發佈”>“部署為網頁應用程序...”,選擇版本並設置權限,然後點擊“更新”
    4. 進入發佈的網頁應用程序,您將看到您所需的CARD及表格。