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, private or public, 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)
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
AND IT WOOOORK!!!!!!!!!!!
