i used your CRUDQ || for updating database in my app but i have a problem is there a way for me to add a blank row ? .
Thanks,
Sai
The script and the CRUD in the app is not designed that way . It requires at least an Id and content in the second column to work. Why would you want an empty row in a database?
That said, my example is just one way, you can of course change how the script and the app work to your liking
I wanted to change because when i clear a row (deleting a row) the number of rows on my Google sheet decrease so I wanna add it every time I clear one.
I don't know how to do it using Apps Script,
but you can always use the built-in Spreadsheet component to achieve this
Sounds like you want it to behave like sqlite or sql, where even if you delete a record, the record id remains. This is possible but will result in many empty items being returned if you request the dataRange, or you may not get back all the data if a script stops at an empty cell.
The CRUD script was designed to mirror AI2 lists, in that if you delete a record all the ids below move up one, maintaining a contiguous dataset.
You could try this in your google apps script
Replace this:
// deletes a single record (and its row) from sheet. Requires row index and col1 to match
else if ( fn == 'DELETE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
sh.deleteRow(parseInt(index)+1);
}
}
return ContentService.createTextOutput("Existing record deleted");
}
with this:
// deletes a single record (and its row) from sheet. Requires row index and col1 to match
else if ( fn == 'DELETE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
var cols = sh.getMaxColumns();
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
var entRow = sh.getRange((parseInt(index)+1),2,1(cols-1));
entRow.clearContent();
}
}
return ContentService.createTextOutput("Existing record deleted");
}
Which will clear the data in the row apart from the index in column 1 (A)
As previoulsy mentioned, not sure how this might impact on the functionality of other elements of the script, or the list returns to the app.
You might also want to consider using:
or as suggested, the native Spreadsheet component
how do you do it with the spreadsheet component because i tried to add a blank row using the add row block and it doesnt work.
Works for me...
Have you set up your service account and downloaded your credentials.json file, then uploaded this to your assets ? You won't be able to change a google sheet unless you do this.
Sorry, I left out a bracket, add a closing bracket to the end, before the semi-colon
How do you do it using a service account
Also I wanna delete the row and add a new one not clear one just to clarify I have bad English I guess
Then use the original DELETE command for that.
You could also try using the UPDATE command by sending empty strings (no changes to script required, just additional blocks)
Read everything, including the links here:
The method is slightly different to the one provided....MIT have never updated it.
Yes, if all blank rows, below the non blank rows have been deleted from the spreadsheet, otherwise, that blank row is already there - pointless.
If you want to INSERT a blank row somewhere in the data, then I believe you will need GAS for that.
yes for this we need Apps Script and its not possible through Spreadsheet component
Does the sheet in question have a column containing a unique key, or are you relying on row IDs to link sheets?
What do you mean by GAS and i wanna add a blank row at the end of the spreadsheet everytime like
if the data in 2 row should be deleted it will delete the row and add a new row at the end of the sheet.