Problem How to use a Google Drive Google Spreadsheet as Database

[Marino Nardio]

can you help me to this point that I do not work (SQLish)

http://puravidaapps.com/spreadsheet.php

"The Google Visualisation API works for public spreadsheets and the best is, you can use it with SQLish commands!
 In the example I used the following query: select C,D where B matches 'Taifun'"

thanks

Marino

[Taifun]

did you create a Google spreadsheet having 4 columns?

what exactly means "does not work"? can you elaborate?

It would really help if you provided a screenshot of your relevant blocks, so we can see what you are trying to do, and where the problem may be.

Taifun

---

Trying to push the limits of App Inventor! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun. 

[Marino Nardio]

insert + Post =works

update, delete, get, select = does not work

Botton Get

Botton Select

This is the spreadsheet

https://docs.google.com/spreadsheets/d/1IEUgkIgKwMZiSmlcISNqhrgUXUHDC0bNOMw7ypJuamY/edit#gid=1441099301

This is the form

https://docs.google.com/forms/d/1cT0CtIgm68GFWfBrTcvbU7p0ajXoXvRXVSF2BW8RtFc/viewform

This is the Blocks

[Taifun]

did you install the Google Apps Scripts, see chapter "How does the UPDATE and DELETE work?" https://puravidaapps.com/spreadsheet.php

also fix your SelectUrl... you are using another id compared to the Get Url... the id must be the same, compare again with my links

Taifun

[Marino Nardio]

I'm sorry, I left the old connections for testing the operation I need.

Now I put the same links

insert + Post and Get =works :D

update, delete, select = does not work :(

what you mean to install " Google Apps Scripts"?

this?....I do not know javascript

[Taifun]

see chapter "How does the UPDATE and DELETE work?" https://puravidaapps.com/spreadsheet.php

Taifun

How does the UPDATE and DELETE work?

I used some lines of Google Apps Script to enable UPDATE and DELETE statements. The example spreadsheet had 3 columns: name, email and message text. I now added another column ACTION to the spreadsheet. Depending on that column, an UPDATE or DELETE will be triggered by the Google Apps Script. The name column is used as key. Of course you also can update or delete multiple rows!

UPDATE example

Define the 4 columns: name="Taifun2", email="my updated email", message="let's update", ACTION="UPDATE" and click "POST" in the App Inventor example app. The script will be triggered and executes the following statement (pseudo code):

   UPDATE spreadsheet SET email="my updated email", message="let's update" WHERE name="Taifun2"

DELETE example

Define the 2 columns: name="Taifun", ACTION="DELETE" and click "POST" in the App Inventor example app. The script will be triggered and executes the following statement (pseudo code):

   DELETE FROM spreadsheet WHERE name="Taifun2"

Preparation

1. In your Google Spreadsheet open the Script Editor via Tools - Script Editor and copy the 3 functions from the source code below.
2. Add a trigger via Ressources - Current Project Triggers. Click the "Add a new trigger" link. In column "Run" select the function "action" from the dropdown list. In column "Events" select "on form submit" and click save.
   
   
3. In case this is your first trigger, you additionally have to authorize its use.
4. Ready!

Script Source Code

//  author: puravidaapps.com
//
// reference documentation
// sheet: https://developers.google.com/apps-script/reference/spreadsheet/sheet
// range: https://developers.google.com/apps-script/reference/spreadsheet/range
function action(e) {
  var key    = e.values[1]; // the entered name, column 2
  var action = e.values[4]; // the entered action (UPDATE or DELETE), column 5
  var sheet  = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  Logger.clear();
  Logger.log('action=' + action);

  if (action == "DELETE"){
    del(key, sheet, values);
  }
  if (action == "UPDATE") {
    upd(key, sheet, values);
  }
}

function del(key, sheet, values){
  Logger.log('DELETE ' + key);
  // http://stackoverflow.com/a/13410486/1545993
  for(var row = values.length -1; row >= 0; --row){
    if (values[row][1] == key){
      sheet.deleteRow(parseInt(row)+1); // loop is 0-indexed, deleteRow is 1-indexed
    }
  } // end: for
}

function upd(key, sheet, values){
  var lastRow = sheet.getLastRow();
  for(i in values){
    if (values[i][1] == key){
      Logger.log('UPDATE i=' + i);
      var rangeToCopy = sheet.getRange(lastRow, 1, 1, 4); // getRange(row, column, numRows, numColumns), do not copy action column
      rangeToCopy.copyTo(sheet.getRange(parseInt(i)+1, 1));
    }
  } // end: for
  sheet.deleteRow(lastRow); // delete last row: this is the update statement
}

[Marino Nardio]

when you paste and save from error on line 6

sorry, I have another question

Always write scripts to control the page: https://puravidaapps.com/spreadsheet.php

but here there are no scripts

why not put the explanation described above?

[Pablo Zjaria]

Hi, 

I don´t know if I have to publish a new thread, but I have a problem (a different one) with the same script:

I implemented this script without problem, just with a small modification: I used it to update a spreadsheet with 21 columns. I put the App working in a production environment and, in a random fashion, the script stopped.

When I realize of this problem I found (always!) a line with the column 22 (the ACTION column) with the word UPDATE and with several rows duplicate, and this rows duplicate were overwritten others rows.

My app updates over 20 rows a day and, in general, it works fine during 10, 15 or 20 days until I found this problem, how I say it occurs in a random fashion. After delete the column 22 content of the row with the issue, the script works ok again (until the next crash...)

Any idea? is there any kind of new version of this script?

thanks in advance

[Taifun]

@Marino, sorry, I do not understand your last question, you might want to elaborate

and if you prefer to adjust the script to your language, make sure you do not add some errors...

@Pablo: sorry, I do not know, why that happens. In case you find a bug in the script, just let me know, so I will update it. Thank you.

Taifun

[Marino Nardio]

Taifun:I asked to put your explanation above

on your page:https://puravidaapps.com/spreadsheet.php

with image printing to help better those hindered like me : )

I made other tests and not greater mistake but still does not work

Perhaps the page (Script Editor) I also have to put the item Action?

[Marino Nardio]

Pablo Zjaria: I may have figured out your problem with several duplicate rows

maybe it's a problem query

When Delete row with number

does not eliminate, but duplicates

solution add letter before the number

unfortunately I can not try it with my Spreadsheet because it does not work

[Taifun]

@Marino, sorry, I do not understand what you are saying

I recommend you to first use the example as it is and if you get it working, then adjust it to your needs

Taifun

PS: please refrain from posting silly animated gifs. thank you 

[Marino Nardio]

Taifun excuse my problem is that I do not know English :(

I use google translator

I did not want to offend anyone, I thought it was funny

I respect very much your work and I thank you for your help

I know that does not work for my problem in understanding

thanks for your help and I hope you can help me again

Greetings

Marine

[tolga sevim]

Hi everybody.

I'm trying to make an inventory app using spreadsheet as database. No problem with posting entries to spreadsheet file but I can't fetch a list from the file which is in a different page, to my app. I want to use the list in a listpicker. I can fetch the inventory entries on the main page to my list but not the list on page2.

Briefly, I want to fetch a list on a specific page in the spreadsheet file.

How can I do that?

I wish I could express myself :)

Thanks in advance.

[Taifun]

 I want to fetch a list on a specific page in the spreadsheet file.

I think, the Google Visualisation API https://developers.google.com/chart/interactive/docs/querylanguage?hl=en and the solution provided here https://puravidaapps.com/spreadsheet.php#select are restricted to get data from the main page only

Taifun

[Kelly Powers]

Hello, 

I used https://puravidaapps.com/spreadsheet.php to test inserting records into a Google Spreadsheet. The Post successful message appears but the spreadsheet is not populated with the inserted values. 

I didn't think that I had to set up a script and I am confused by the call to getAction  from the When butnPost Click event occurs.

The action for our app will always be an insert. I can see how the call to post works to the form . However, what makes the form get submitted? 

I am also using the Emulator. We do get a successfully posted message but no entries? Any suggestions?

Kelly

[Taifun]

you will have to follow the preparation steps EXACTLY to avoid issues

 

I didn't think that I had to set up a script 

correct, for insert only you do not need Google Apps Script

I am confused by the call to getAction  from the When butnPost Click event occurs.

I don't understand, can you elaborate?

However, what makes the form get submitted? 

the Web.PostText method will do that

what happens, if you click the Submit button in the form manually? do you get values inserted into the spreadsheet?

I am also using the Emulator. 

you might want to test using a device so we could exclude emulator issues then...

Taifun

PS: probably next time you might want to start a new thread instead of opening an old one?

[Kelly Powers]

Thank you Taifun, 

I started the form from scratch, checking the settings, etc. Everything is now working!

Kelly