Sample Code

Text validation

Document -> Sample Code for Using APIs -> Sample code for validation -> Text validation

In the method loadData of SheetAPI, it allows user add text 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,4,4],"type":1}],"opt":{"dt":1,"op":8,"txt":"ok","hint":"ok","allow":true},"id":"validation-1"}"
    }],
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:\"ok\"}},
         {sheet: 1, row: 2, col: 3, json: {data:\"test\"}},
         {sheet: 1, row: 2, col: 4, json: {data:\"that is ok\"}},
         {sheet: 1, row: 3, col: 2, json: {data:\"ok123\"}},
         {sheet: 1, row: 3, col: 3, json: {data:\"fine\"}},
         {sheet: 1, row: 3, col: 4, json: {data:\"cool\"}},
         {sheet: 1, row: 4, col: 2, json: {data:\"me too\"}},
         {sheet: 1, row: 4, col: 3, json: {data:\"cool\"}},
         {sheet: 1, row: 4, col: 4, json: {data:\"too\"}}
    ]
});

The above code will insert a range of cells from B2:D4 by adding text 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.

Contain, Not Contain, Equal, Is valid email, Is valid URL 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,4,4],type:1}],opt:{dt:1,op:8,text:\"ok\",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 1 if it is text validation
      op        // 8 means Contain. 
                // 9 means Not Contain. 
                // 6 means Equal.
                // 10 means Is valid email.
                // 11 means Is valid URL.
      txt       // the text string to be compared  
      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