Clear Form script with IF function for selected items

[Electrical Dept]

Hey, I have the following script for an in house ordering sheet and want to be able to clear the form of the items that have been picked and the ones that are on backorder to remain. I have tried with the following script but it still clears the entire form:

function ClearCells() {

if ('E3:E50' >= true); {

var sheet = SpreadsheetApp.getActive().getSheetByName('Order Form');

sheet.getRange ('A3:A50').clearContent();}

if ('E3:E50' >= true); {

var sheet = SpreadsheetApp.getActive().getSheetByName('Order Form');

sheet.getRange ('C3:H50').clearContent();}}

To me it should only clear those that have been ticked as done (use checkboxes in google sheets).

Am I doing something wrong or just asking too much from this script? I want it to clear all except those on backorder.

[💼]

Your “if logic” makes no sense and the entire code is duplicate, unnecessary and makes little sense. Where are you trying to read from when comparing incorrectly with >= to true?

On Jul 19, 2023, at 3:07 PM, Electrical Dept <Elect...@rvia.com.au> wrote:

Hey, I have the following script for an in house ordering sheet and want to be able to clear the form of the items that have been picked and the ones that are on backorder to remain. I have tried with the following script but it still clears the entire form:

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cced28d4-ecf8-497b-b01c-f219b4bf0cf5n%40googlegroups.com.

[Michael O'Shaughnessy]

Well.... I am not sure what you are trying to test in your IF statement.  You have 'E3:E50' which is a string and you are comparing it to a boolean value of "true".  I cannot think of any time this would ever be true.  So, both your IF functions are evaluating to FALSE.

Also, this function is receiving NO arguments so what are you actually testing?

Try this real quick:  right before the third line of your function add "console.log("Got here!!") and try it again.  Look at your console and see if you did infact "get here".

I think you will find you will need to work on your IF statement test.

So, you need to find the value that you need to test and send it to this function.

Also, I cannot think of any situation where I would use a test of ">=" to a boolean.  Usually, boolean's are either True or False.  (And yes, the super gurus are going to tell me that I am wrong... but for simple applications this works for me.)

[Electrical Dept]

Thanks for the replies. (I have done this script through google searches as I am very new to all this.)

I have this google sheets form:

What I want to do is hit the "Clear Form" button and it clear the form except for those marked as "Out Of Stock" so that they remain and I won't have to manually delete all the information of the picked items.

I have 2 instances of the clear script because the B column has a formula and the script deletes the formula (discovered this when I first did the script).

The "IF" function may be the wrong choice but not sure what will be the right one.

I tried using just = but it came up with errors and errors went away when I added >= (but the script still cleared the entire form).

[Martin Molloy]

You need to loop through the rows of data and take action on each row separately.

So, there will be other ways to do this but this works 

function myFunction() {

  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Order Form').getRange("a1:H")

  var data = range.getValues()

  var len = data.length

  //then loop through the array

  for(var i =1; i<len-1; i++){

    var checkBox = data[i][4] // 4 if the checkbox value is in column E

    // clear the contents

    if(checkBox == true){

        data[i][0] ='' // this clears column A

        data[i][2]= '' //This clears column C

        data[i][3]= '' //This clears column D

        data[i][4]= '' //This clears column E

        data[i][5]= '' //This clears column F

        data[i][6]= '' //This clears column G

        data[i][7]= '' //This clears column H

    }

  }

  //then write the data back

  range.setValues(data)

  SpreadsheetApp.flush()

}

Hope that helps 

Martin

[Electrical Dept]

Thanks heaps for that. I tried that and it works great.

[Electrical Dept]

Update: Just realised that the script deletes the formula in column B (even though it says not to delete column B). Without that formula the sheet doesn't work properly.

[Martin Molloy]

Skipping column B

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Order Form') //

  var lastRow = sheet.getLastRow()

  var rangeAB = sheet.getRange(3,1,lastRow,2) // from row 3 Column A

  var dataAB = rangeAB.getValues()

  var rangeCH = sheet.getRange(3,3,lastRow,6)  //From row 3 column C

  var dataCH = rangeCH.getValues()

  var len = dataAB.length

  //then loop through the array

  for(var i =0; i<len-1; i++){

    var checkBox = dataCH[i][2] // 4 if the checkbox value is in column E

    // clear the contents

    if(checkBox == true){

        dataAB[i][0] ='' // this clears column A

        dataCH[i][0]= '' //This clears column C

        dataCH[i][1]= '' //This clears column D

        dataCH[i][2]= '' //This clears column E

        dataCH[i][3]= '' //This clears column F

        dataCH[i][4]= '' //This clears column G

        dataCH[i][5]= '' //This clears column H

    }

  }

  //then write the data back

  rangeAB.setValues(dataAB)

  rangeCH.setValues(dataCH)

  SpreadsheetApp.flush()

}

If you still have problems with it could you share a version of your spreadsheet - it'll be easier to spot the pitfalls

Martin

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/64ad7f34-f8bc-47e0-8471-beef7cb5aea8n%40googlegroups.com.

[Michael O'Shaughnessy]

Just adding some to this....  I would suggest 2 things:

1. For column B formula use an array literal in cell B1.  Something like this: ={"Part No.", ARRAYFORMULA(IFNA(VLookup(A3:A....."}  As long as row one doesn't get deleted this will keep the formula there.

2. Use Array.filter().  After you get the data into an array filter it for the out of stock == true.  Then clear everything from row 2 to the end THEN paste the filtered values in.

I use #1 A LOT because I have built spreadsheets for others to use and they ALWAYS delete stuff from row2.  Since there is a header row MOST everyone will leave that alone. This trick saves me A LOT of time and headaches!!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2PgrYsENwZE-kfgjJpBLz91tL4pEke6uYppvu0ZzE_GJXA%40mail.gmail.com.

[Electrical Dept]

Hey Martin,

Finally got a chance to change over the coding and it's doing weird things. Keeps the unchecked data but deletes the formulas in column B but keeps the data that the formula inputted there. It is also deleting the ones marked out of stock and I need to keep those in there too.

BEFORE SCRIPT:

AFTER SCRIPT:

Here is a copy of the google sheets file: https://docs.google.com/spreadsheets/d/1VY7qdd2gU4f5Oy-hxCmd2lkJ5P7kX_dd4sKOuZMvlVc/edit?usp=drive_link

[Keith Andersen]

Clarification

Do you want to see only those that are picked AND out of stock or do you want to see ALL picked records and out of stock ones too?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6730fba8-5936-42a9-8844-f32482dfd2een%40googlegroups.com.

[Mark Battersby]

G’day,

 

Want to only see those not picked and those that are marked Out Of Stock. Ones that are marked as Picked I want to delete while keeping the formula in column 2.

 

This form is for internal parts ordering and would be more efficient to be able to clear the form of all that have been picked and leave those that are either out of stock or not picked yet.

 

Mark Battersby

RVIA Electrical Supervisor

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> on behalf of Keith Andersen <keith.a...@gmail.com>
Date: Thursday, 3 August 2023 at 1:44 pm
To: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Clear Form script with IF function for selected items

Clarification

 

Do you want to see only those that are picked AND out of stock or do you want to see ALL picked records and out of stock ones too?

 

 

 

On Wed, Aug 2, 2023 at 5:30 PM Electrical Dept <Elect...@rvia.com.au> wrote:

Hey Martin,

 

Finally got a chance to change over the coding and it's doing weird things. Keeps the unchecked data but deletes the formulas in column B but keeps the data that the formula inputted there. It is also deleting the ones marked out of stock and I need to keep those in there too.

 

BEFORE SCRIPT:

 

AFTER SCRIPT:

 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/rwTG2AFRTtU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4whA6DCCkpfQDFbC1QJWsunJ6_U42N5QAp%2B7jx4_9XNHQ%40mail.gmail.com.

[Keith Andersen]

Try this....

function clearCells2(){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Order Form');

var lastRow = sheet.getLastRow();

var startRow = 3;

var data = sheet.getRange(startRow, 1, lastRow-2, 8).getValues();

for( i = 0; i < data.length; i++){

if(data[i][4] != true || data[i][4] == "" || data[i][5] != true || data[i][5] == ""){

//sheet.getRange(i+3, 9).setValue("Yes");

sheet.getRange(i+3, 1).clearContent();

sheet.getRange(i+3, 3).clearContent();

sheet.getRange(i+3, 4).clearContent();

sheet.getRange(i+3, 5).setValue(false);

sheet.getRange(i+3, 7).clearContent();

sheet.getRange(i+3, 8).clearContent();

}//---ends if statement

}//---ends for loop

}//---ends function

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/SY7P282MB39479B52290C6A109792CDE48D08A%40SY7P282MB3947.AUSP282.PROD.OUTLOOK.COM.

[Keith Andersen]

I actually applied it to the Copy sheet you supplied/shared. I put a yellow button "Try This" to execute the new function.

Cheers

[Electrical Dept]

Hey Keith,

Thanks heaps for that!

Just tried that one and works really well except I discovered that if one is marked as just "Out Of Stock" then it clears it too.

Tried a few changes to the code you wrote up but it just made things worse.

[Keith Andersen]

So...

Picked alone - erase

Picked + Out of Stock save

Out of Stock - save

Is that to total of your criteria?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5dc70577-b642-469c-9533-7747dbd2aab6n%40googlegroups.com.

[Electrical Dept]

Yep, That is what is needed.

[Keith Andersen]

Ok. I'll revise it.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c8053d85-423c-4825-a7b2-d6f4f3967095n%40googlegroups.com.

[Keith Andersen]

Try it now.

[Electrical Dept]

Yep, those criteria work well now...but forgot about leaving the ones that aren't picked need to remain too. Hit the button and it deletes everything except those that are marked Out Of Stock. So all the new orders placed which haven't been marked picked or out of stock are deleted too.

[Keith Andersen]

Eric- give it a try now

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5c24b688-7ce4-45e2-9efe-a9f5dcf7915dn%40googlegroups.com.

[Electrical Dept]

Thank You so much guys! Works like a treat.

[Keith Andersen]

Good deal.

Any problems crop up feel free to reach out.

Cheers

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a1de3abf-24f7-4be9-b073-a9917af72c9dn%40googlegroups.com.