How to put the sheet name in a cell depending on another cell changes?

Question

I have a Google sheets to change the App store content with each version of our app, for example: changing the App description, changing the App icon, changing the keywords, etc. However, we don't change all of these items with every update. My goal is to have the "C" column track which of these in column "B" was last changed in what version and put the sheet name in the "C" column from that sheet onwards so that it updates automatically instead of me doing so manually.

So basically, it would be something like: If B2 changed, put the sheet name in C2 from this sheet onwards without affecting the previous sheets. and if C2 = the current sheet name, highlight C2.

I'm sorry if I couldn't explain it well, but hopefully, my sheet will help get the situation better

Sheet Link

• I managed to get the sheet name from an app script, but I couldn't link it with the "B" column, but it get the name only once and doesn't update it if the sheet name is changed or when I duplicate it. Here's the function:

function mySheetName() {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return key;
}

• I tried to use the =Cell formula, but that didn't have what's required.
• Tried searching for a get-around but didn't find anything similar to my current situation.

Answer 1

I created a simple example that should solve your problem. Just add this auxiliary functions on your app script, and set the values on "C" column to:

=IF(GetPreviousSheetValue(ROW(), COLUMN()-1, dummy!$A$1) <> INDIRECT(ADDRESS(ROW(), COLUMN()-1)), GetSheetName(), GetPreviousSheetValue(ROW(), COLUMN(), dummy!$A$1))

The app script functios are:

function onEdit(e) {
  SpreadsheetApp.getActive().getSheetByName('dummy').getRange('A1').setValue(Math.random());
}

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

function GetAllSheets() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets();
}

function GetAllSheetNames() {
  var out = new Array();
  var sheets = GetAllSheets();
  for (var i = 0 ; i < sheets.length; i++) {
    out.push( [ sheets[i].getName() ] );
  }
  return out;
}

function GetPreviousSheetName(dummy) {
  let sheetName = GetSheetName();
  let allSheetNames = GetAllSheetNames();
  for (var i = 0 ; i < allSheetNames.length; i++) {
    if (sheetName == allSheetNames[i] && i + 1 < allSheetNames.length) {
      return allSheetNames[i + 1];
    }
  }
  return "";
}

function GetPreviousSheetValue(row, col, dummy) {
  let sheetName = GetSheetName();
  let allSheets = GetAllSheets();
  for (var i = 0 ; i < allSheets.length; i++) {
    if (sheetName == allSheets[i].getName() && i + 1 < allSheets.length) {
      return allSheets[i + 1].getRange(row, col).getValue();
    }
  }
  return "";
}

PS: Since google cache the functions return values, we have to add a dummy parameter to GetPreviousSheetName and GetPreviousSheetValue so these functions re-run and update the cells correctly whenever the sheets changes. For more info take a look at link

And this is the end spreadsheet: Image1 Image2