Sample Code

List item validation

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

Here comes the code example

    fileName: 'Validation Json File',
    sheets: [{
        name: 'First tab',
        color: 'orange',
        id: 1
        name: 'Second tab',
        id: 2
    floatings: [{
         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.

NameProperty ExampleDescription
File namefileName fileName: 'Example Sheet' Define the file name for this sheet file.
Sheet objectsheets sheets: [{},{}] Set defined sheet tabs information for this file.
Floating objectfloatings floatings: [{},{}] Set defined floatings information for this file.
Cell objectcells 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
      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



Copyright © FeyaSoft Inc. All rights reserved.