VisualNEO Web

Google sheets data loader 2.1

Based on:

https://github.com/chriszarate/sheetrock

Valid querys:

https://developers.google.com/chart/interactive/docs/querylanguage

More info:

https://visualneo.com/forum/topic/neogsheets-google-sheets-plugin

Sample App:
https://visualneo.com/tutorials/neogsheets

Description

The neoGSheets plugin for VisualNEO Web seamlessly integrates Google Sheets, enabling applications to access, display, and manipulate data stored in Google Sheets in real-time. This plugin is ideal for applications that require dynamic data loading, such as dashboards, inventory systems, or any app needing live data updates. Here are the key functionalities and applications of the neoGSheets plugin:

Data Loading and Retrieval:Using neoGSheetsLoad, developers can pull in data from a Google Sheet, loading it as JSON to be displayed or manipulated within the app. This function is particularly useful for dynamically populating lists, tables, or reports with data sourced directly from a spreadsheet.

The plugin also offers the neoGSheetsLoadColumn command, allowing developers to load data from a specific column. This feature is perfect for applications where a subset of data, such as a list of names or categories, is needed without loading the entire dataset.

Data Filtering and Querying: With SQL-like querying capabilities, neoGSheetsLoad enables developers to filter data on specific parameters before loading, making it easy to display only relevant information. For instance, you could retrieve data only for "active" entries or filter results by date range, which is highly beneficial for data-driven applications requiring focused views.

Real-Time Data Sync: The plugin’s ability to access live data from Google Sheets makes it easy to maintain up-to-date information within applications. This real-time access is valuable for collaborative applications where multiple users update data, such as team projects or shared databases, ensuring users always have the latest information.

Flexible API Configuration: Developers can manage which Google Sheet and specific range to access, allowing for efficient data use and control over the app’s data footprint. By specifying columns or setting query parameters, the plugin helps in optimizing data retrieval to maintain efficient performance.

The neoGSheets plugin for VisualNEO Web enables the use of Google Visualization API’s query language, allowing you to filter and process Google Sheets data dynamically. This language is similar to SQL and allows complex data manipulation right from the Google Sheet. Here’s how to use queries with the neoGSheets plugin, along with some common examples.

Basic Syntax for Queries in neoGSheets

To apply a query with the neoGSheetsLoad command:

neoGSheetsLoad "gsheetsURL" arrayVariable "query" callbackSubroutine

• gsheetsURL: The public URL of your Google Sheet.
• arrayVariable: Variable to store the JSON results.
• query: SQL-like string to filter or manipulate data.
• callbackSubroutine: Optional subroutine that executes once data is loaded.

Query Examples

Here are some sample queries to help you get started:

1. Selecting Specific Columns

SELECT A, B, C

This query retrieves only columns A, B, and C. Useful for applications that only need certain columns instead of loading the full sheet.

2. Filtering Rows Based on Conditions

SELECT A, B, C WHERE D = 'Active'

Retrieves rows where column D contains "Active," helping to filter data like active items in inventory or current events.

3. Sorting Results

SELECT A, B ORDER BY B DESC

Sorts data by column B in descending order, useful for displaying data like scores, dates, or priorities in a specific order.

4. Limiting Results

SELECT A, B LIMIT 10

Limits the results to 10 rows, ideal for showing a preview or limited dataset, such as recent entries or top 10 items.

5. Using Aggregations

SELECT C, SUM(D) GROUP BY C

Groups data by column C and calculates the sum of column D, often used for aggregating data like totals by category.

Sample Command with Query

To retrieve data where only active records are shown, sorted by date:

neoGSheetsLoad "gsheetsURL" "dataArray" "SELECT A, B, C WHERE D = 'Active' ORDER BY C DESC" myCallback

This command fetches columns A, B, and C for rows marked as "Active" in column D, ordering them by the date in column C.

Important Notes

• Queries are case-insensitive (SELECT and select are equivalent).
• Use single quotes (' ') for text values in the WHERE clause.
• Make sure your Google Sheet is published and public for the plugin to access it.
  
  

This approach allows you to efficiently tailor your app’s data needs, minimizing load times and improving performance by only retrieving essential data. For further information on complex queries, refer to the full Google Visualization API Query Language documentation.

neoGSheetsLoad

neoGSheetsLoadColumn

neoGSheetsLoadCell

neoGSheetsLoadAsTable

neoGSheetsLoadByName

neoGSheetsGetMetadata

Created with the Personal Edition of HelpNDoc: Make the switch to CHM with HelpNDoc's hassle-free WinHelp HLP to CHM conversion tool