search

appsmithorg / appsmith

Platform to build admin panels, internal tools, and dashboards. Integrates with 25+ databases and any API.
https://www.appsmith.com
Apache License 2.0
32.45k stars 3.51k forks source link

[Feature]: Schema for unstructured databases like Google Sheets #10705

Open leotom2000 opened 2 years ago

leotom2000 commented 2 years ago

Is there an existing issue for this?

Summary

Need to have a schema implementation for unstructured databases. It can allow users to define the datatype of columns. This can solve data inconsistent problem by narrowing the options for users to add data of different datatype on a same column.

Implementation: (1) Provide a User Interface popup in the query page to preview / define / override the column datatype. (2) Validate button can preview existing data and auto suggest column datatype.
(2.1) Validate function also alerts data inconsistencies that would have been added manually. (3) Should prevent users from adding inconsistent data.

Why should this be worked on?

Data stores like google sheets cannot define datatype for a column, at the same time they accept any datatype data for a column. so working with different datatype on the same column gives error while fetching condition based queries.

Front logo Front conversations

nidhi-nair commented 2 years ago

Related #7794

yan-hic commented 1 year ago

I have a list of invoice "numbers" in GSheet. When using it as source for a table, and the first row is truly numeric, any non-numeric values get stripped at API-level. I can see in my Fetch SheetRows RAW result that AS strips string values for invoice as it could not coerce to Number.

An invoice number is actually more an ID, and can be in any string-format e.g. ABC-123

Workaround:

  1. create a dummy second (after headers) row with values of 1 or a
  2. add a row offset of 1 to the Fetch API -> luckily AS autodetects before offsetting so it uses the 1 and a to guess the column datatype, respectively Number and Plain Text
trialsin commented 1 year ago

I've that problem too. Using the workaround for now!

yan-hic commented 1 year ago

Unfortunately the workaround of a dummy top row does not always work. I am using Connected Sheets in GSheets for a way to read BigQuery data (BQ -> Sheets -> A/S) and the extracted data cannot be changed, hence cannot insert a row.

Has this feature request got any traction ?