Google sheet Webapp | Data Entry Form and capture Signature Form nhập liệu có ký tên (Ver 2)

26/06/2024 giaoanppt

Giaoan.link chia sẻ đến các bạn một project mới về Google sheet Webapp, Data Entry Form and capture Signature Form nhập liệu có ký tên (Ver 2). Với một giao diện mới, project này bạn có thể ứng dụng trong công việc. Phần khác và mới ở đây là khung ký tên, chữ ký sẽ được lưu dạng insert hình ảnh chứ không phải lòa image từ Google Drive. Bạn có thể xem video hướng dẫn và code apps script ở bên dưới.

||Bạn tìm kiếm nhiều project về excel, apps script ở đây

XEM VIDEO HƯỚNG DẪN TRÊN YOUTUBE

Các Project ngẫu nhiên về excel và apps script:

Mã trang “Code.gs”

const SETTINGS = {
  APP_NAME: "Data Entry Form with Signature",
  SHEET_NAME: {
    RESPONSES: "Data"
  },
  HEADERS: [
    { key: "timestamp", value: "Thời gian" },
    { key: "id", value: "ID" },
    { key: "name", value: "Họ và Tên" },
    { key: "add", value: "Địa chỉ" },
    { key: "phone", value: "Số Điện thoại" },
    { key: "gender", value: "Giới tính" },
    { key: "city", value: "Thành phố" },
    { key: "date", value: "Năm sinh" },
    { key: "signature", value: "Chữ ký" },
  ]
}

function link(filename) {
  return HtmlService.createTemplateFromFile(filename).evaluate().getContent()
}

function doGet() {
  return HtmlService.createTemplateFromFile("index.html")
    .evaluate()
    .setTitle(SETTINGS.APP_NAME)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

function submit(data) {
  data = JSON.parse(data)
  const headers = SETTINGS.HEADERS.map(({value}) => value)
  const id = Utilities.getUuid()
  const signatures = []
  const values = SETTINGS.HEADERS.map(({key}, index) => {
    if (key === "id") return id
    if (key === "timestamp") return new Date()
    if (!key in data) return null
    if (Array.isArray(data[key])) return data[key].join(",")
    if (data[key].startsWith("data:image")) {
      signatures.push(index)
      return SpreadsheetApp.newCellImage().setSourceUrl(data[key]).build().toBuilder()
    }
    return data[key]
  })
  const ws = SpreadsheetApp.getActive().getSheetByName(SETTINGS.SHEET_NAME.RESPONSES) || SpreadsheetApp.getActive().insertSheet(SETTINGS.SHEET_NAME.RESPONSES)
  ws.getRange(1,1, 1, headers.length).setValues([headers])
  const lastRow = ws.getLastRow()
  ws.getRange(lastRow + 1, 1, 1, values.length).setValues([values])
  signatures.forEach(index => {
    ws.getRange(lastRow + 1, index + 1).setValue(values[index])
  })
  return JSON.stringify({success: true, message: `Cám ơn bạn đã gửi! ID: ${id}`})
}

Mã trang “index.html”

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <link href="https://fonts.googleapis.com/css?family=Roboto:100,300,400,500,700,900" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/@mdi/font@6.x/css/materialdesignicons.min.css" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/vuetify@2.x/dist/vuetify.min.css" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet"
  integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no, minimal-ui">
</head>

<body>
  <form id="app" class="container" style="width: 700px">
    <v-app>    
          <v-form ref="form" @submit.prevent="submit" :disabled="loading">
            <v-card :loading="loading" outlined>              
              <div>                 
                <img src="https://lh3.googleusercontent.com/pw/AP1GczN86oZ_W5_eMYlpNeef5ozd84DzE9flzP8Ovn9RY5N93U0nyXCBmt1PHOT_MJgXmg8CdyKu0E4-LHKRsOIvU1SMH7rIpPgkqbzmcRaUWFynMnKB33E=w2400" width="100%"/>  
                <div style="height: 5px;"></div>
                <div>
                  <div class="mb-1">
                    <div class="form-check form-check-inline">
                      <label style="font-weight: bold;" class="form-label">Họ và tên:</label>  
                    <my-input :item="form.name" class="form-control form-control-sm" style="width: 200px; height: 60px"> </my-input>
                    </div>        
                    <div class="form-check form-check-inline">
                    <label style="font-weight: bold;" class="form-label">Số Phone:</label>  
                    <my-input :item="form.phone" class="form-control form-control-sm" style="width: 165px; height: 60px"> </my-input>
                    </div>                    
                    
                    <div class="form-check form-check-inline">
                    <label style="font-weight: bold;" class="form-label">Năm sinh:</label>                         
                    <my-input :item="form.date"class="form-control form-control-sm" style="width: 165px; height: 60px" ></my-input>
                    </div>            
                  </div>  

                  <br>
                  <div class="mb-1">
                    <div class="form-check form-check-inline" >
                      <label style="font-weight: bold;" class="form-label">Địa chỉ:</label>  
                    <my-input :item="form.add" class="form-control form-control-sm" style="width: 620px; height: 60px"></my-input>
                    </div>
                  </div>  
                 
                  <br>                 
                  <div class="mb-1">
                  <div class="form-check form-check-inline">
                  <label style="font-weight: bold;" class="form-label">Giới tính:</label>  
                  <my-input :item="form.gender" style="width: 150px;"></my-input>
                  </div>    
                  <div class="form-check form-check-inline">
                  <label style="font-weight: bold;" class="form-label">Thành phố:</label>  
                  <my-input :item="form.city" style="width: 150px; "></my-input>
                  </div>            
                  <div class="form-check form-check-inline" >
                    <label style="font-weight: bold;" class="form-label">Chữ ký:</label>  
                    <my-input :item="form.signature"></my-input>
                  </div>
                  </div> 
                </div>                   
                  <v-col cols="12">
                    <v-btn type="submit" color="primary" :disabled="loading" large depressed>Submit</v-btn>
                  </v-col>
                </div>
              </div>
          </v-form>

          <v-snackbar v-model="snackbar.show" :color="snackbar.color" bottom>
            {{ snackbar.message }}
            <template v-slot:action="{ attrs }">
              <v-btn color="white" text v-bind="attrs" :timeout="snackbar.timeout" @click="snackbar.show = false">
                <v-icon>mdi-close</v-icon>
              </v-btn>
            </template>
          </v-snackbar>
    </v-app>
  </form>
  <?!= link("Javascript.html"); ?>
</body>

</html>

Code trang “Javascript.html”

<script src="https://cdn.jsdelivr.net/npm/signature_pad@2.3.2/dist/signature_pad.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vue@2.x/dist/vue.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vuetify@2.x/dist/vuetify.js"></script>

<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"
  integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous"></script>
  
<script>
  const apiCall = function (functionName, params = {}) {
  params = JSON.stringify(params);
  return new Promise((resolve, reject) => {
    google.script.run
      .withSuccessHandler((response) => resolve(JSON.parse(response)))
      .withFailureHandler((error) => reject(error))
      [functionName](params);
  });
};

const getFormData = (form) => {
  const data = {};
  Object.entries(form).forEach(([key, item]) => {
    data[key] = item.value;
  });
  return data
};

const form = {
  name: {
    
    type: "text",
    value: "",
    disabled: false,
    placeholder: "",
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  add: {
    
    type: "email",
    value: "",
    disabled: false,
    placeholder: "",
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  phone: {
    
    type: "tel",
    value: "",
    disabled: false,
    placeholder: "",
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  gender: {
    
    type: "select",
    value: "",
    items: ["Nam", "Nữ", "Other"],
    disabled: false,    
    placeholder: "",
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  city: {
    
    type: "select",
    value: "",
    items: ["Cà Mau", "Bạc Liêu", "Sóc Trăng", "Cần Thơ", "Tiền Giang"],
    disabled: false,
    placeholder: "",
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  date: {
    
    type: "date",
    value: "",    
    disabled: false,
    placeholder: "",    
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
  signature: {
    label: "",
    type: "signature",
    value: "",
    disabled: false,
    placeholder: "Mở khung ký số",
    items: [],
    rules: [(v) => !!v || "Bắt buộc điền!"],
  },
};

const MySnackbar = Vue.component("my-snackbar", {
  template: `
    
  `,
  props: {
    show: true,
    message: "",
    color: "",
  },
  data: () => ({
    snackbar: this.show,
    timeout: 5000,
  })
})

const MySignature = Vue.component("my-signature", {
  template: `
    <div>
      <v-select
        v-model="item.value"
        :label="item.label"
        :placeholder="item.placeholder"
        :rules="item.rules"
        :type="item.type"
        :items="item.items"
        @click="openPad"
        small-chips
        filled
        ></v-select>
      <v-dialog
        v-model="dialog"
        width="400"
        eager
      >
        <v-card>
          <v-card-title class="text-h5 primary white--text">
            Khung ký số{{item.label}} 
          </v-card-title>
          <v-card-text class="pa-0">
            <canvas :ref="item.label" width="400" height="200"/>
          </v-card-text>
          <v-divider></v-divider>
          <v-card-actions>
            <v-spacer></v-spacer>
            <v-btn
              color="primary"
              text
              @click="savePad"
            >
              Done
            </v-btn>
            <v-btn
              color="error"
              text
              @click="clearPad"
            >
              Clear
            </v-btn>
            <v-btn
              color="grey"
              text
              @click="closePad"
            >
              Close
            </v-btn>
          </v-card-actions>
        </v-card>
      </v-dialog>
    </div>
  `,
  props: {
    item: Object,
  },
  data: () => ({
    dialog: false,
    show: false,
    signaturePad: null,
  }),
  methods: {
    openPad: function(){
      this.dialog = true
      const label = this.item.label
      this.signaturePad = new SignaturePad(this.$refs[label])
      if (this.item.value) this.signaturePad.fromDataURL(this.item.value)
    },
    closePad: function(){
      this.dialog = false
    },
    clearPad(){
      this.signaturePad.clear()
    },
    savePad(){
      if (this.signaturePad.isEmpty()) {
        this.item.value = null
        this.item.items = []
      } else {
        this.item.value = this.signaturePad.toDataURL()
        this.item.items = [{
          text: `Signed at ${new Date().toLocaleString()}`,
          value: this.item.value
        }]
      }
      this.signaturePad.clear() 
      this.dialog = false
    }
  }
})

const MyInput = Vue.component("my-input", {
  components: {MySignature},
  template: `
    <my-signature v-if="item.type === 'signature' ":item="item"></my-signature>
    <v-autocomplete
      v-else-if="item.type === 'select'"
      v-model="item.value"
      :label="item.label"
      :placeholder="item.placeholder"
      :rules="item.rules"
      :type="item.type"
      :items="item.items"
      :multiple="item.multiple"
      small-chips
      filled
      ></v-autocomplete>
    <v-text-field
      v-else
      v-model="item.value"
      :label="item.label"
      :placeholder="item.placeholder"
      :rules="item.rules"
      :type="item.type"
      
      ></v-text-field>
    `,
  props: {
    item: Object,
  },
});



new Vue({
  el: "#app",
  vuetify: new Vuetify(),
  data: () => ({
    loading: false,
    title: "Data Entry Form with Signature",
    subtitle: "Hiện đại, tùy biến dễ dàng --> free code!",
    form,
    snackbar: {
      show: false,
      message: "",
      color: "",
      timeout: 5000,
    },
  }),
  methods: {
    showSnackbar: function({message, color}){
      this.snackbar.message = message
      this.snackbar.color = color
      this.snackbar.show = true
    },
    submit: async function () {
      if (!this.$refs.form.validate()) {
        return this.showSnackbar({message: "Form is invalid", color: "warning"})
      } 
      this.loading = true;
      const data = getFormData(this.form);
      try {
        const result = await apiCall("submit", data);
        this.loading = false;
        this.$refs.form.reset()
        this.showSnackbar({message: result.message, color: "success"})
      } catch (error) {
        this.loading = false;
        this.showSnackbar({message: error.message, color: "error"})
      }
    },
  },
});
</script>