appsmith icon indicating copy to clipboard operation
appsmith copied to clipboard

Published 20 hours ago • verified publisher icon appsmithorg

[Feature]: Schema for unstructured databases like Google Sheets

Open leotom2000 opened this issue 3 years ago • 1 comments

Is there an existing issue for this?

  • [X] I have searched the existing issues

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

leotom2000 avatar Jan 28 '22 07:01 leotom2000

Related #7794

nidhi-nair avatar Feb 14 '22 12:02 nidhi-nair

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

yan-hic avatar Dec 06 '22 19:12 yan-hic

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

trialsin avatar Dec 22 '22 19:12 trialsin

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 ?

yan-hic avatar Jan 20 '23 16:01 yan-hic