Sample Code

List item validation

Document -> Sample Code for Using APIs -> Sample code for validation -> List item validation

In the method loadData of SheetAPI, it allows user add list item validation to the sheet. .

Here comes the code example


SHEET_API.loadData(SHEET_API_HD, {
    fileName: 'Validation Json File',
    sheets: [{
        name: 'First tab',
        color: 'orange',
        id: 1
    },{
        name: 'Second tab',
        id: 2
    }],
    floatings: [{
         sheet:1, 
         name:"validation1",
         ftype:"cdt",
         json: "{"name":"vd","rng":[{"span":[1,2,2,3,4],"type":1}],"opt":{"dt":3,"op":0,"list":["Apple","Orange","Banana","Kiwi"],"hint":"Fruit","allow":true},"id":"validation-1"}"
    }],
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:\"ok\"}},
         {sheet: 1, row: 2, col: 3, json: {data:\"apple\"}},
         {sheet: 1, row: 2, col: 4, json: {data:\"orange\"}},
         {sheet: 1, row: 3, col: 2, json: {data:\"test\"}},
         {sheet: 1, row: 3, col: 3, json: {data:\"banana\"}},
         {sheet: 1, row: 3, col: 4, json: {data:\"kiwi\"}}
    ]
});

The above code will insert a range of cells from B2:C4 by adding list item validation.

For validation, loadData json object includes at least 4 names: fileName, sheets, floatings and cells.

Name	Property	Example	Description
File name	fileName	fileName: 'Example Sheet'	Define the file name for this sheet file.
Sheet object	sheets	sheets: [{},{}]	Set defined sheet tabs information for this file.
Floating object	floatings	floatings: [{},{}]	Set defined floatings information for this file.
Cell object	cells	cells: [{},{}]	Set defined cell information for each tab.

list validation

To add text validation, the following json object need to be added into floatings.

{
     sheet:1,              // the sheet id of added condition
     name:"validation1",   // name should be unique
     ftype:"cdt",          // define validation type
     json: "{name:\"vd\",rng:[{span:[1,2,2,3,4],type:1}],opt:{dt:3,op:0,list:[\"Apple\",\"Orange\",\"Banana\",\"Kiwi\"],hint:\"ok\",allow:true}},\"id\":\"validation1\"}"
  }

For each json object inside validation floating, it is a string with the following format.


  name       // vd
  span       // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added validation
  opt 
      dt        // data type - should be set as 3 if it is list item validation
      op        // 0 
      list      // the list item string separated by comma.   
      hint      // the display message for invalid data
      allow     // true or false. If it is true, it means allow enter invalid data, but show hint if it is invalid.
      ignoreBlank  // true - default is false if it is not set. If cell is blank, not apply validation if ignoreBlank is set true.
  id         // unique in this sheet file