Clear a cell and fetch the Google sheet name

Question

I want to make a script to clear a fixed cell on all sheets but the first four and fill the very same cell with the sheet name.

So far I have a script to blank out the cell and the fill it with a new function the fetches the sheet name. The first script is triggered on opening the spreadsheet. However, it just says loading… and does not fetch the sheet names.

My current (non-working script):

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}


function clearRange() {
SpreadsheetApp.getActive().getSheets()
    .map(function (s, i) {
        if (i > 3) s.getRange('B3').clearContent().setFormula('=sheetName()');
    })
}

Any great ideas? Thanks

niwox · Answer 1 · 2015-11-03T11:28:41.683

Same method but a little modified

function clearRange(n, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.map(function(sheet,index){
    if (index > n) sheet.getRange(range).clearContent();
  });
}

// call the function
function run(){
  clearRange(3, 'B3')
}

You can pass an argument n and range instead of the hard coded 3 and 'B3' in case you want to use the function with other parameters

score 0 · Accepted Answer · answered Nov 03 '15 at 12:23

Instead of writing a custom function to the cell, why don't you write the sheet name itself ? Like so...

function clearRange() {
SpreadsheetApp.getActive().getSheets()
.map(function (s, i) {
    if (i > 3) s.getRange('B3').clearContent().setValue(s.getName());
    })
}

If needed you can trigger this function to run onOpen..

NOTE: I think you can even leave out the .clearContent() part as the content will be overwritten any how.

Clear a cell and fetch the Google sheet name

2 Answers2