Delete SQL statement using google app script withwout the form

[Allysha_April]

Hello, I am trying to delete some row of data from my google sheet using a query function and I have read taifun's tutorial on it but he used a on form submission and was wondering if I didn't use it, would it make a difference. Would the script have to be different as well? This is because I already have a query script for the sheet where it helps to read the data and sort is ascendingly. Here are my blocks for for just calling the data, I want something similar but to delete like using the query "DELETE from Sheet1 where A matches 'data', B matches 'data':

Here is my script:

[TIMAI2]

As you may have seen from the documentation for the Google Query Language there are no "action" SQL functions such as INSERT or DELETE.

What you can do is add another function into your web app to handle the deletion of rows and send the request from the app: (assumes aData will be found in Column A and bData will be found in Column B)

function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var aData = e.parameter.aData;
var bData = e.parameter.bData;
var rg = sh.getDataRange().getValues();

for ( var row = rg.length-1; row>=0; row-- ) {
if ( rg[row][0] == aData && rg[row][1] == bData ) {
sh.deleteRow(parseInt(row+1));
}
}

Set a Web1.Url with your parameters and use Web1.GET

[Allysha_April]

Thank you for the script, I have emplimented it in the doGet function as so:

and here are my blocks for it, it doesn't work though. I'm not sure if I did the blocks right...

[TIMAI2]

aData and bData are supposed to be strings and not lists - oh, I can see that is what you are sending.

So what is not working.....

I didn't add a return section to the doGet(e) ?

function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var aData = e.parameter.aData;
var bData = e.parameter.bData;
var rg = sh.getDataRange().getValues();

for ( var row = rg.length-1; row>=0; row-- ) {
if ( rg[row][0] == aData && rg[row][1] == bData ) {
sh.deleteRow(parseInt(row+1));
}
return ContentService.createTextOutput("Rows Deleted");
}

Try that.

[Allysha_April]

Ouuhh okay I didn't know that but how can I change it to a string when I am trying to extract it from a list then?

I have entered the return code into the script but then this error pops up when I try to run the app...

[TIMAI2]

Hmmm

Remove any call to querySheet(e) in your doGet function.

I am assuming you are only using doPost(e) for your other web app call ?

[Allysha_April]

Yup, I use it to select all the data in the table and sort it by the dates.

I have removed them but now whenever I run the app this error pops up:

[TIMAI2]

Please show the blocks that call the "WebApps" web component (your "POST"), and also a full copy of your web app script, in text (not an image), and an image of your data on the spreadsheet.

[Allysha_April]

WebApps blocks:

My web app script:

doGet(e){
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var aData = e.parameter.aData;
var bData = e.parameter.bData;
var rg = sh.getDataRange().getValues();
for(var row = rg.length-1; row>=0; row--){
if(rg[row][0] == aData && rg[row][1] == bData){
sh.deleteRow(parseInt(row+1));
}
}
}
function doPost(e){
return querySheet(e);
}
function querySheet(e){
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = e.parameter.SQL;
var qry = '=query(' + rg +',"' + sql + '",1)';
var ts = ss.insertSheet();
setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
var outString = '';
for (var row= 1;row < getResult.length; row++){
outString += getResult[row].join(',') + '\n';
}
return ContentService.createTextOutput(outString);
}

my google sheet: all the format are strings

[TIMAI2]

Ahh, that is a GET too. Let me do some work....

[Allysha_April]

Okay thank you so much this is the only bit left for me to finish my Final Year Project and i'm sorry I do not know how to put a code in a listview thing? in kodular there's one button that shows </> but in here when I click on it, it doesn't seem to help make the code easier to read

[TIMAI2]

(for the coding just type three backticks then return, enter your code, then return, then three more backticks)

Right

web app script. Change it to this:

function doPost(e){
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var aData = e.parameter.aData;
var bData = e.parameter.bData;
var rg = sh.getDataRange().getValues();

for(var row = rg.length-1; row>=0; row--){
if(rg[row][0] == aData && rg[row][1] == bData){
sh.deleteRow(parseInt(row+1));
}
}
}

function doGet(e){
return querySheet(e);
}

function querySheet(e){
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = e.parameter.SQL;
var qry = '=query(' + rg +',"' + sql + '",1)';

var ts = ss.insertSheet();
setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);

var outString = '';
for (var row= 1;row < getResult.length; row++){
outString += getResult[row].join(',') + '\n';
}
return ContentService.createTextOutput(outString);
}`

All I have done is switched things around, so that your original script continues to use doGet and the new delete scripting will use the doPost.

You will need to change the blocks for the Delete:

(note: there is NO ? before ID (the first parameter) when using POSTText)
Try all that and see how it goes....

[Allysha_April]

Thank you so much for the help, I really appreciate it

[TIMAI2]

Glad you got it working OK