CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Williamszr98
Williamszr98

Reputation: 17

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

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?

Upvotes: 0

Views: 41

Answers (2)

Williamszr98
Williamszr98

Reputation: 17

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.

Upvotes: 0

dwmorrin
dwmorrin

Reputation: 2734

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.

Upvotes: 1

Related Questions