How to clear cells if ISBLANK?-CodePudding

I want to add clearing function. If "B1" is blank, then clear C1,D1,E1,F1;

But I don't know how I can do it. I tried custom function in formatting, but not works. Can u help me?

CodePudding user response：

You can hide, but not clear, the values in columns C:F with this conditional formatting custom formula rule:

=isblank($B1)

The rule should set the text color to match the cell fill color, making the value in the cell "invisible".

CodePudding user response：

The simple answer to your question is as follows:

function myFunction() {
    let your_sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/your_sheet_id').getSheetByName('Sheet1')
    let cell_check = your_sheet.getRange('B1');
    if(cell_check.isBlank()) {
        your_sheet.getRange('C1:F1').clearContent();
    }else{
      console.log('not empty')
    }
}

But I would probably advise considering the use of numbered ranges instead of named ranges as it will be easer to reason about at scale if you are running through multiple rows.

function myNumberedRangeFunction() {
    let your_sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/your_sheet_id').getSheetByName('Sheet1');
    let cell_check = your_sheet.getRange(1,2);
    /* getRange(
            1, the first row,
            2, is the second column
        )
    */
    if(cell_check.isBlank()) {
        your_sheet.getRange(1,3,1,4).clearContent();
        /* getRange(
            1, the first row,
            3, the third column,
            1, get one row, 
            4, get four columns
        )
    */
    }else{
      console.log('not empty')
    }
}

Typically, if I am manipulating a large spreadsheet, I will pull the entire spreadsheet data_table = your_sheet.getRange(1,1,1,1).isBlank() ? [] : Array.from(your_sheet.getRange(1,1,your_sheet.getLastRow(),your_sheet.getLastColumn()).getValues()); and convert it to a table array that I can analyze and manipulate all at once and then just place the changes all at once with your_sheet.getRange(1,1,data_table.length,data_table[0].length).setValues(data_table)