Intructions to Setup Flow for Importing Data from Google Sheet to Form Family

FLOW:

A. SETUP GOOGLE SHEET

Google Sheet Template: CLICK HERE (Just for references. Do not clone for usage because new script is usually tested on)

1. Sheet

  • required/bắt buộc điền: yes or no. This row is to config which data is required. Leave blank is understood as no. Optionally (The first column is required as default)
    Update 2020-05-05: we can use some of javascript syntax as condition for setting required, which is listed in part 3
  • datatype: required, currently supports 4 types:
    • text
    • number
    • time: time should be written in 24-hour format and an apostrophe (’) in front.
      • Example: '15:00
    • date: follows format YYYY-mm-dd

2. Google App Script (GAS)

Code: https://rtgit.rta.vn/rta_thiendao/rtst-internship-program/blob/master/GAS/GGS_TO_FORM/GAS_IMPORT_TO_RTA.gs

* You should go to “Run Enable new Apps Script runtime powered by Chrome V8” in order to get this script work!

In Google Sheet Template, go to Tools -> Script editor -> ImportGGSData2RTA.gs

  • Put your sheets that need validating data into sheet_list_name

  • You also need to modify this code block to get information for the Webhook

3. HTML File

File: https://rtgit.rta.vn/rta_thiendao/rtst-internship-program/blob/master/GAS/GGS_TO_FORM/RTAConfirmForm.html

4. Some Javascript syntax for handling required
https://www.w3schools.com/jsref/jsref_obj_string.asp

Comparison Operators, Logical Operators, Bitwise Operators: https://www.w3schools.com/js/js_operators.asp

Arithmetic Operators: https://www.w3schools.com/js/js_arithmetic.asp

String Methods: https://www.w3schools.com/js/js_string_methods.asp

Number Methods: https://www.w3schools.com/js/js_number_methods.asp

Dates: https://www.w3schools.com/js/js_dates.asp

Math function: https://www.w3schools.com/js/js_math.asp

I will go details later

B. ACTION JOB

ACTION TEMPLATE: https://rtgit.rta.vn/rta_thiendao/rtst-internship-program/blob/master/GAS/GGS_TO_FORM/AC_IMPORT_DATA_FROM_GGS_TO_FORM.xlsx

This AC does 3 jobs:

  1. Push spreadsheet link and sheet id to a dm (e.g. dm_import_sample_spreadsheet_info)
  2. Update datasource (using Spreadsheet link and Sheet id to get data from GGS)
  3. Generate and Submit XML to Form Family

Follow this help to know how to get template for Action Type generate_xml_instances: https://help.rta.vn/t/rtcloud-ac-action-generate-xml-instances-from-datamodels/1061

C. DATASOURCE

DATASOURCE RSCRIPT (USE IN RESET MODE):

sample_ggs_data_1.R: https://rtgit.rta.vn/rta_thiendao/rtst-internship-program/blob/master/GAS/GGS_TO_FORM/sample_ggs_data_1.R

sample_ggs_data_2.R: https://rtgit.rta.vn/rta_thiendao/rtst-internship-program/blob/master/GAS/GGS_TO_FORM/sample_ggs_data_2.R

1 Like