Simple CRUD with Google Sheets and Apps Script (fixes Hay Kel flaws...)

I have lost count of the number of times the community has received requests for help from developers, after following the Hay Kel series of videos on CRUD with google sheets.

The videos are well produced, and offer the viewer a good insight to creating the app-sheet-script ensemble required for CRUD activities.

However, the script is flawed: a bound to sheet script, with code that only allows the bound spreadsheet and use of the first sheet; hard-coded columns and hard-coded row data for updating, which means you must have the data set as per the provided sheet; block settings for sheetId and sheetname which will not work because of the bound spreadsheet coding mentioned above.

(I have repeatedly asked Hay Kel to fix this, but my posts on youtube have just been deleted)

This guide aims to address these issues, by providing a google apps script that will work standalone or bound, and require the developer to provide the sheetId and sheetname, meaning any spreadsheet owned by the script owner can be used, and the script will also work with a spreadsheet containing any number of columns. I have created an example app to work with the script and the sheet that will replicate (to some extent) the example app created in the videos, to simplify transference of ideas. The only major change is to not use the get single record part of the script (the user already fetches all the data with Read All, no need to fetch the data again). I have left an example of single record return in place for completeness.

SCRIPT

Apply this script to your google apps script project, deploy as you (your google account) to execute, and available to "Anyone". get the script url generated.

You should not need to edit the script in any way in order for it to work.

function doGet(e) {

var ss = SpreadsheetApp.openById(e.parameter.SHEETID);
var sh = ss.getSheetByName(e.parameter.SHEETNAME);
var rg = sh.getDataRange().getValues();


  //READ ALL RECORDS
  if ( e.parameter.FN == "ReadAll" ) {
    return ContentService.createTextOutput(JSON.stringify(rg)).setMimeType(ContentService.MimeType.JSON);
  }

  //READ SINGLE RECORD
   else if ( e.parameter.FN == "ReadRecord" ) {
    var ref = sh.getRange(parseInt(e.parameter.ROWID)+1,1,1,rg[0].length).getValues();
    return ContentService.createTextOutput(JSON.stringify(ref)).setMimeType(ContentService.MimeType.JSON);
  }
  
  //DELETE SINGLE RECORD
   else if ( e.parameter.FN == "Delete" ) {
    sh.deleteRow(parseInt(e.parameter.ROWID) + 1);  
    return ContentService.createTextOutput("Record Deleted");  
 } 

  //UPDATE SINGLE RECORD
   else if ( e.parameter.FN == "Update" ) { 
    var data = JSON.parse('[' + e.parameter.DATA + ']');
    sh.getRange(parseInt(e.parameter.ROWID) + 1,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput("Record Updated");
  }

  //CREATE NEW RECORD  
  else if ( e.parameter.FN == "Create" ) { 
    var data = JSON.parse(e.parameter.DATA);
    sh.appendRow(data);
    return ContentService.createTextOutput("New Record Appended");
  }

}
Update:

You can, if you wish, make a small addition to the script, which will help to return the display values in the sheet, instead of values. This is useful when working with formatted dates/numbers/texts

Change this line of the script

var rg = sh.getDataRange().getValues();

to

if ( e.parameter.VALTYPE == "D") {
var rg = sh.getDataRange().getDisplayValues();
} else {
var rg = sh.getDataRange().getValues();
}

then, in your blocks, add a new variable called VALTYPE, and include this in your baseurl. Set the variable to "D" if you want display values, set it to blank or anything else, if you want the values.

haykelScript.txt (1.3 KB)

SHEET

I provide an example set of data you can use in your spreadsheet

HKFixData.csv (944 Bytes)

BLOCKS / APP

I used just the one web component and one notifier in order to handle options / choices. Follow the block structures carefully to see how the web url is built for each function in the script, especially for Update and Create. An example app is provided, you need to add your script url, sheetID, and sheetname to the variables.

HKFix_blank.aia (9.1 KB)

VIDEO

I am hopeful that this example script, sheet and app are of use in overcoming the shortfalls in the Hay Kel offering.

Of course, any questions or concerns, please ask below

12 Likes

15 posts were merged into an existing topic: Data read modify Googlesheet Sheet3

Good Morning!

I delete my all try (6projects) and i start new time :smiley: AND IT WOOOORK!!!!!!!!!!!

LOT OF THANKS!!!!!!!

THANK YOU THANK YOU THANK YOUUUUUU!!!!!!!! :blush: :blush: :blush: :blush: :blush: :blush: :innocent: :innocent: :innocent: :innocent: :innocent:

Data send is okay data rad is okay, but if googlesheet value is date txt box value is date + T22:00:00......

what i can do? :slight_smile:

1 Like

See the first post SCRIPT > UPDATE
for an alternative script that gets display values

2 Likes

Thanks for the offers (sheet csv, aia block & others), I tried yours now and everything work perfect. Thanks so much, you just cure my headache.

3 posts were split to a new topic: Login & Register with Google Sheets and Google Apps Script

good morning
I have followed all the indicated steps and it gives me the following error when I run from MIT App Inventor in test mode

" The operation remove list item cannot accept the arguments: , ["<!doctype"], [1] "

I think the error refers to this point

Can you help me please

thanks in advanced

1 Like

Your problem is upstream with the google apps script. Check the script is correct, ensure you have deployed/re-deployed the latest version.

We would need to see the full content of responseContent to be able to advise further on what the error is.

Sorry for ask you to decode the response Content.
The Content is obviously not a json.

You are the best, thanks you very much, from DR...!!!!

A post was merged into an existing topic: Open an excel sheet