I have a excel workbook template with 4 worksheets. All sheets have the same structure and data from these 4 sheets goes into the same table. There are 40 columns and about 30 rows of data in each sheet. Each row in the spreadsheet is stored as one record in the table. Every cell value has to validated before inserting into the database. These validations could be checking for 1. Empty values, 2. If the value is in a given range of values, 3. check if the value exists in the database, 4. value could be used to query the database for other info etc.
Could you please let me know what functions in the Cells API should be used to perform this export.
How do I attach validations for each cell value or how do i implement validations while exporting the spreadsheet data.
I have a lot of named ranges defined in the spreadsheet template which refer to a single cell or a set of rows and columns (as a table). How do I traverse through these ranges. What is the equivalent to Excel's HLookup and VLookup functions?
Appreciate if you could provide some code samples or pointers to the documentation for each of the above tasks.