Delete SQL statement using google app script withwout the form

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:
image

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

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

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






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) ? :upside_down_face:

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.

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...
image

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 ?

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:
image

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.

WebApps blocks:
image


image

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
image

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

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 :sweat_smile:

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

image

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....

1 Like

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

Glad you got it working OK :+1:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.