🟩 Google Sheet - CRUDQII - create/read/update/delete/query - using google web app

You can refer this script method

@TIMAI2
I have created one apps using CRUDQII.
I have face a big problem When I updated my record data it's work properly but if I use coma ( , ) any text box then record data automatic transfer next column. Please look at the picture.
What can I do now?


With the script as it is, it uses "," as a delimiter to split the full row of data. The simple answer is, do not use commas in your data. (You could include a check in your blocks to either remove or replace them before sending to google web app).

Alternatively, rewrite the google apps script to manage the uploaded data in a different way.

1 Like

How I can use check in block?
Or
How can I modify sheet script?
Please tell me details.

Two ways with blocks

image

You would need to change the blocks to save the data as a JSON array instead of a csv row, then edit the google apps script to handle the stringified JSON array on receipt.

1 Like

Hello Tim.
I am studying your CRUD II script, I am liking it, thank you...
one question, re: ID, the global variable. From where does the information hail?

My confusion comes from embedded vs not embedded scripts/files. My programming background is from the 80's, things 'ave changed a little. :no_mouth:

If I understand you correctly:

Google refer to standalone and bound scripts. The bound scripts are...bound to a spreadsheet and they cannot be unbound, the standalone scripts are....standalone. (is this what you mean by embedded/not embedded?)

A bound script can allow for some methods in the script that relate directly to the spreadsheet with which it is bound. The one we tend to use the most is:

var ss = SpreadsheetApp.getActive();

which gets the spreadsheet to which the script is bound.

With a standalone script, we will need to specify more precisely:

var ss = SpreadsheetApp.openById('1tKzb0PuqJWsWDSfrTFsqBM60S8n_5ulw48wfRF10SZk');
or
var ss = SpreadsheetApp.getByUrl('https://docs.google.com/spreadsheets/d/1tKzb0PuqJWsWDSfrTFsqBM60S8n_5ulw48wfRF10SZk');

You can use these two methods in a bound script for the bound spreadsheet, or any other spreadsheet.

You should see the spreadsheet ID in the two methods (is this what you are referring to by ID ?) The ID is the unique reference to the file, and can be found in the url address bar when you open a spreadsheet.

Does this answer your queries ?

Hey Tim. Most of my confusion is syntax, as opposed to the theory.

You did clear that up a bit though, thank you.

I was asking for the source of the global variable 'id' in the app CRUD, because it is different than the web1 url value. It is long and cryptic thus its origin is mystifying.

Cheers

P.s. it's not the revised crud, but first version.

hmmm, the only reference I can see to id in the original CRUD guide is in the spreadsheet:

={"id";ArrayFormula(if(B2:B<>"",row(A2:A)-1))}

If you mean this one in CRUDQII

image

That is a spreadsheet ID

You are correct... I am confused. Ha ha... too much studying for one day, ok.

I figured it out. Gotta specify NEW not version when publishing.

That very much depends....

Thanks Tim. I realized that saving it as a New script, not a version no., makes the difference.
Question, how does one isolate a record in a gsheet, and delete a row?
I am having a little difficulty with the script part. The record would be selected from a ListView, which would correspond with the records. (I can always do a double check within the script, just in case)
Passing through a ListView.SelectionIndex would not correspond to a row number... would it be that simple??

cheers

There is a delete function in the script:

what is the corresponding AI2 block pattern for that?
I keep getting invalid URL errors.

If the user chooses to update the record, I'd like to carry the data through, delete the existing record then re-add the updated information. That way, If there turns out to be a delete record option, I can keep it deleted then it then.
For now I do not want to delete records, but can always do that on paperwork day.
Sometimes it is convenient to update a record while on site.

Cheers

p.s. Just saw I have an ampersand, not a question mark.

Seems you are not using the CRUDII aia / blocks... ?

In essence, to delete a record/row, you need to make sure you have the correct row with the correct data (that no-one else has deleted or added records/rows), if these match, then delete that row. This is all tested with the script, which check the row number and the first entry (cell content) in that row, against the data you selected to delete in the app.

As long as record and List View align... correct?

I am adapting your crud blocks, dissecting them so I can get a deeper understanding.

Do you see something in the blocks there that would provide me an incorrect URL?
Error 1109: The specified URL is not valid.

??

Yes, you do not appear to be setting the Websave.Url

It seems I have gone backwards now. I cannot even safe a record to the gsheet.
Will have to dissect a little more, get that function running, and then try again with the delete/update. I need to brush up on JS some more.

The account owner of the google apps script would need to create a spreadsheet for each user, and provide the spreadsheet file ID to each user. All the spredsheets would reside on the account owners google drive.

Or, as previously suggested: