Google表單-簡易資料庫

這篇教學完整的說明如何運用google表單與google script製作一個簡易的表單資料庫

http://blog.jim60105.com/2015/06/google-database.html

doGet()取資料

function doGet(e) {
    // 取得權限
    var params = e.parameter;
    // 宣告變數
    var name = params.name;
    var tel = params.tel;
    var mail = params.mail;
    var company = params.company;

    // 取得表單-填上表單ID
    var SpreadSheet = SpreadsheetApp.openById('12QzjBnZuvWJut7DODdt-f83LDT0sYVhAVTCX6kgg618')

    // 寫入表單第幾個標籤
    var Sheet = SpreadSheet.getSheetByName('報名表1');

    // 定義最後一行-之後將寫入資料陸續往後寫入
    var LastRow = Sheet.getLastRow();

    // 寫入姓名
    Sheet.getRange(LastRow + 1 , 1).setValue(name);
    // 寫入電話
    Sheet.getRange(LastRow + 1 , 2).setValue(tel);
    // 寫入mail
    Sheet.getRange(LastRow + 1 , 3).setValue(mail);
    // 寫入公司行業
    Sheet.getRange(LastRow + 1 , 4).setValue(company);

  //信件範本
  var htmlbody = "Hi Admin: <br/><br/>有訂單拉，檢查一下吧！ <br/><br/>" + name + '<br/><br/>Send by Google Apps';

  //mail格式
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "測試用標題" ,
    htmlBody: "您有一筆新的表單資訊<br/><br/>" +
              "公司及姓名:" + name + "<br/><br/>" +
              "電話:" + tel + "<br/><br/>" +
              "mail" + mail + "<br/><br/>" +
              "公司業種" + company + "<br/><br/>" 
  })
}

debug()偵測-測試寫入

function debug() {
    var Result = doGet({
        "parameter":{
            "name": "debugname",
            "tel": "0953-111-111",
            "mail": "[email protected]",
            "company": "debugCompany"
        }
    })
    Logger.log('Result: %s', Result)
}
}

HTML

<style>
    .col-xs-4.col-xs-offset-3 input[type="checkbox"]{
        display: inline-block
    }
</style>
<div class="col-xs-4 col-xs-offset-3">
    <div class="form-group">
        <label for="gformname">公司及姓名</label>
        <input type="mail" class="form-control" id="gformname">
    </div>
    <div class="form-group">
        <label for="gformname">電話</label>
        <input type="tel" class="form-control" id="gtel">
    </div>
    <div class="form-group">
        <label for="gformname">mail</label>
        <input type="tel" class="form-control" id="gmail">
    </div>
    <div class="form-group">
        <h6>公司業種</h6>
        <div class="checkbox"><label><input type="checkbox" class="gcheck" value="民生消費">民生消費</label></div>
        <div class="checkbox"><label><input type="checkbox" class="gcheck" value="醫美科技">醫美科技</label></div>
        <div class="checkbox"><label><input type="checkbox" class="gcheck" value="醫美科技">醫美科技</label></div>
        <div class="checkbox"><label><input type="checkbox" class="gcheck" value="零售業">零售業</label></div>
        <div class="checkbox"><label><input type="checkbox" class="gcheck" value="食品業">食品業</label></div>
        <div class="form-group">
            <label for="othercompany">其他</label>
            <input type="text" class="form-control" id="othercompany">
        </div>
    </div>
    <input type="button" value="送出" onclick="SendScore()">
</div>
<script>
    function SendScore() {
        // 判斷其他有沒有被填寫
        var Gcheck = []

        if($('#othercompany').val() != ''){
             Gcheck = $('#othercompany').val()
        }else{
            Gcheck = $('.checkbox .gcheck:checked').map(function(){
                     return $(this).val()
                     // 取字，轉字串
                 }).get().join()
        }
        console.log(Gcheck)
        $.get('https://script.google.com/macros/s/AKfycbw8OIp1StbrwVeZQIPoWA30I8G8VsS5KhuNSBSQDLxrlQtHloo/exec',{
            "name": $('#gformname').val(),
            "tel": $('#gtel').val(),
            "mail": $('#gmail').val(),
            "company": Gcheck
        },
        function(data){
             document.write("--------------------------");
            document.write("Result = "+data);
            document.write("--------------------------");
        })
        location.replace("/");
    }
</script>