Problem How to use a Google Drive Google Spreadsheet as Database

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

"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'"

--

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.

-- 

insert + Post =works

update, delete, get, select = does not work

Button Get

Button 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

help please!

dbAppInventor

-- 

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

-- 

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

-- 

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

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
}

--

when you paste and save from error on line 6

-- 

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

-- 

@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: 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?

Pablo Zjaria:I have problem to run 4 columns and you complain about a problem with 20 columns.....

To make fun of me?

-- 

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

-- 

@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

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

-- 

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

-- 

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 :)

--

 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 solution provided here https://puravidaapps.com/spreadsheet.php#select are restricted to get data from the main page only

--