Efficient way to check if an array of cells is not the desired value

Question

My row 1 is the header row and I have an onOpen() function that runs to make sure all the header titles are correct.

    onOpen(){

        var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
        s.getRange("A1").setValue("Order Number");    
    }

I have about 20+ of these headers, so every time I open it will run 20+ times regardless if the header exists or not, it will either fill or overwrite it. As you can see that's very inefficient, and I was wondering if there's a way to reduce the amount of time it runs based off of the cell's value.

I've tried a switch statement in a loop to check if the text is there and matching or not, but the running time is about the same. So is there a more efficient way to do it while optimizing running time?

Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Aug 01 '19 at 20:20

score 1 · Accepted Answer · answered Aug 02 '19 at 02:24

1

If you've got one sheet with 20+ headers, all with predetermined values, then just set them all at once.

function onOpen() {
  var headers = [
    ["Order Number", "Customer Name", /* etc... 20 more */, "Shipping Address"]
  ];
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
    getRange(1, 1, 1, headers[0].length).setValues(headers);
}

What about preventing them from changing in the first place?

In the sheet, you can click "Tools > Protect sheet" and setup warnings or custom permissions to protect that range.

answered Aug 02 '19 at 02:24

dwmorrin

2,704
8
17

The problem with protecting the range is that the person is not allowed to use the filter for that header anymore. Also, I think I need a loop or else it will all print "Order number" for the first row. – Williamszr98 Aug 06 '19 at 16:36
@Williamszr98 ok, I can adjust my answer, but I need a clearer idea of what you are trying to do. Please update your question with: do you have 20+ cells in your 1 header row, or 20+ sheets with header rows?, Also clarify what you want to print for the first row, in your current question it looks like you want the first row, first cell to say "order number". – dwmorrin Aug 06 '19 at 17:31

score 0 · Answer 2 · answered Aug 06 '19 at 17:41

I figured out how to set the column values on the first row:

var headers = [[
    "Order",
    "Name",
    "Date",
    "Address"
    ]];
  // headers 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily output").getRange(1,1,1, headers[0].length).setValues(headers);

This should set the values for each column for the first row, with only one call to the class.

Efficient way to check if an array of cells is not the desired value

2 Answers2