Delete rows from Google Sheets based on multi column criterea

MIT App Inventor Help
1

How would I be able to achieve this ??
I want to delete all rows having the SAME value in Column D, where Value in Column B = Value in Column C

DELETE SCRIPT
DELETE SCRIPT960×720 64.4 KB

function onFormSubmit() {
ss = SpreadsheetApp.getActive();
sh = ss.getSheetByName('Form responses 1');
rg = sh.getDataRange().getValues();
if ( ??????????????????) {
sh.deleteRows(????????????); //deletes all rows having same Coln D, where Coln B = Coln C
}
}

2

This is not really an App Inventor question.....

function should be something like this (not tested):

function onFormSubmit() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Form responses 1');
var rg = sh.getDataRange().getValues();

//find each matching row and "blank it"
for ( var  i = 0; i<rg.length; ++i ) {
if ( rg[i][1] === rg[i][2] && rg[i][3] == 25) {
sh.getRange(i+1,1,1,5).setValues([["","","","",""]]);  //,,setValues array might need changing
}
}

//find the blank rows and delete them
for ( var i = 0; i<rg.length; ++i ) {
if ( rg[i][0] == "" ) {
sh.deleteRow(rg[i]);
}
}
3

GIving an error when saving the Script

SyntaxError: Unexpected token '=' (line 7, file "Code.gs")

Also this is just test data. The script should be able to read the data and then identify which rows to delete

4

Sorry should be var i = 0 not var = i, have edited original function in two places

5

SyntaxError: Unexpected token '=' (line 14, file "Code.gs")

for ( var = i; i<rg.length; ++i ) {

6

I ammended the sript, it is saved. I created the trigger also onFormSubmit

But when the data comes in, it is deleting just that one row. Not all Rows, having the same file Number

function onFormSubmit() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Form responses 1');
var rg = sh.getDataRange().getValues();

//find each matching row and "blank it"
for ( var i = 0; i<rg.length; ++i ) {
if ( rg[i][1] === rg[i][2] && rg[i][3] == 25) {
sh.getRange(i+1,1,1,5).setValues([["","","","",""]]); //,,setValues array might need changing
}
}

//find the blank rows and delete them
for ( var i = 0; i<rg.length; ++i ) {
if ( rg[i][0] == "" ) {
sh.deleteRow(rg[i]);
}
}
}

FILE TRACK 1
FILE TRACK 1960×720 72.8 KB

Is is deleting values, not the row. Subsequent data comes in leaving blank row.

Also when next row data has come in, it is not checking for B=C. It shud have deleted the rows, where file token is 30, because Coln B = Coln C

FILE TRACK 2
FILE TRACK 2960×720 70.5 KB

7

You need to decide how to get the "Token" value into your script, or if it is needed at all?

Anyway here is a working script with Token value hardcoded to 25:

function condRowDel() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('delrows');
var rg = sh.getDataRange().getValues();
var rows = [];

for ( var i = 0; i<rg.length; ++i ) {
if ( rg[i][1] === rg[i][2] && rg[i][3] == 25) {
sh.getRange(i+1,1,1,5).setValues([["","","","",""]]);
}
}
var newrg = sh.getDataRange().getValues();
for ( var i = 0; i<newrg.length; ++i ) {
if ( newrg[i][0] == "" ) {
sh.deleteRows(i+1,1);
}
}
}
8

File Token Value will be taken only if value in Column B = Value in Column C

Only then ALL rows with that File Token Value will be Deleted

This script is also only deleting the last row uploaded with File Token. It shud delete ALL ROWS with File Token = 25 (in this case). Only value in cells is deleted. Leaves a blank row when subsequent data comes in

9

Tx for ur response and efforts

Ur script did not work the way I wanted it to , but it did give me a direction to work on

Been mulling over it since then and trying various tutorials. I guess finally I got it to work the way I wanted it to

10

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