Quote from luishp on May 30, 2020, 10:47 am

@bguk all the work must be done within Google Sheets. Just follow the steps carefully and, if you still have problems, share the Google Sheets with me and I will take a look. You must keep the timestamp field and the script as they are in the example sheet.
Just uncomment the email code line (at the beginning of the script) if you want to receive an email with every new row added to the Spread Sheet. The web app publishing procedure is the key point.

Regards.

Quote from Gaev on May 30, 2020, 4:15 pm

@luishp

This facility has opened up the many possibilities for Apps to access server side data, which can be added, modified, deleted by the owner of the data as easily as updating a spreadsheet ... a few side benefits that caught my eye were the use of Google functions called GOOGLEIMAGE() for using the sheet to get around CORS restrictions, GOOGLETRANSLATE() and GOOGLEFINANCE() ... the latter for stocks listed on NYSE/NASDAQ.

But the Google API has two deficiencies ...

a) the sheet needs to be shareable to the public ... there are ways to restrict access, and this is a topic for another post

b) the Google Sheets Query language requires references to the columns to be their ID's (like A, B, C) and not the fields names (in row 1)

Background:

As you know, I have been exploring ways for developers to specify column/field names (instead of ID's) in their query strings i.e. instead of specifying something like ...

select A,B,C where D > 50

... specify ...

select field:name, field:country, field:profession where field:age > 50
or
select @@name, @@country, @@profession where @@age > 50

This way, if columns in the target sheet are inserted, deleted or moved around, the Apps do not have to be modified.

I have managed to accomplish this in two steps i.e. invoking ...

select * LIMIT 0

... and extracting the column headers from the response ... and then doing a StrReplace of the field names with column ID's before sending the request using neoGSheetsLoad.

I have been exploring ways of combining this into one step, so the first step is transparent to the developer ... but I have hit road blocks having to do with timing and synchronization (the developer could initiate multiple requests with field names BEFORE the (LIMIT 0) response was received.

Questions:

Assuming that the end solution (new plugin command) would look something like ...

neoGSheetsLoadByName "[sheetURL]" [data] "[queryStringUsingNames]" "responseSubroutine"

... would this (new) plugin command have the ability to invoke the first query (select * LIMIT 0) ... and upon receiving a response ...

- do the extraction/association of field names with column ID's
- replace field names with associated column ID's in the developer request string
- invoke the regular neoGSheetsLoad command using the required (by Google) column ID's

 

Alternatively, is there any way for a request parameter values to be fed to a responding subroutine ... so e.g. in a command like  ...

neoGSheetsLoadByName "[sheetURL]" "subroutineToReceiveHeaders" [data] "[queryStringUsingNames]" "responseSubroutine"

... the result of "select * LIMIT 0" would be passed to subroutineToReceiveHeaders along with (and this is key) [data] "[queryStringUsingNames]" "responseSubroutine" as parameters ... that way, the interim subroutine could do the heavy lifting of resolving field names ... and issuing the normal neoGSheetsLoad command using the parameter values.

With Javascript (promises and .then() methods) this can be achieved ... but with the current setup, neoGSheetsLoad only feeds the received data to the specified subroutine.

Failing that, the solution would be to place the responsibility for timing/synchronization on the developer ... e.g. have the developer check x times every n milliseconds for the variable containing fieldName/columnID pairings (for a particular URL) to be populated, before doing the StrReplace and subsequent request using Column ID's ... in order for this scheme to work in instances where multiple requests were initiated before the variable was populated, the requests would have to be stacked (queued up) in an array of some sort ... I am not sure many developers would want to get into this kind of complexity.

Any other ideas ?

Thanks again.

 

Quote from luishp on May 31, 2020, 11:13 am

@gaev Attached is the plugin source code for you (or anyone interested) to take a look.
I'm currently very busy but this seems to me extremely interesting. Will be back to this as soon as I can.
Thank you!

PD: Check the embedded .js file path before compiling the plugin.

Quote from Gaev on May 31, 2020, 3:26 pm

@luishp

Thanks ... I have examined the source code of your plugin ... looks like it can be done ... I say "looks like" because I am not sure about calling a plugin command (i.e. neoGSheetsLoad) from the callback function code of another plugin command (like neoLoadGSheetsByname) ... will try out version 1 later today ... when that works, I will look into making the field prefix (@@, field: etc.) user defineable ... might also allow user to specify array for fieldName/columnID pairings.

Thanks.

 

Quote from Gaev on June 1, 2020, 6:11 am

It works ... just have to do a bit more testing, before turning it back to @luishp for inclusion in next release of plugin.

Quote from Vadim on June 3, 2020, 6:41 pm

Luis, thank you so much! Great new features!

Quote from farhad2008 on September 4, 2020, 10:22 pm

hi,@luishp

How to change the value of cells?

and Is there an example for update and append?

regards

Quote from luishp on September 5, 2020, 8:33 am

@farhad2008 this plugin only read content from Google Sheets. However you can append new data using a VisualNEO Web Form (as described in this thread) or using Google Forms.
The latest version of this plugin is included with VisualNEO Web 20.8.22 and above.

Regards.

Quote from farhad2008 on September 5, 2020, 3:03 pm

Hi,@luishp

 

I was able to run the google-sheets-save.neoapp instance and it gave me OK, but nothing was saved in the sheet. Why?

Regards

Quote from farhad2008 on September 5, 2020, 11:17 pm

Hi,@luishp

 

Thanks for your help I solved the problem.

Regards