2

1st time question asker, so I'm apologizing in advance if my question is worded wrong or doesn't make sense or is, frankly, to wordy (I tend to over explain).

I have a sheet with, currently, 10 tabs. All of them are a work-in-progress and can change at any point in time based on what I'm doing with them. It's for a game that you can level up different parts of your character and things are added every now and then.

I have on the Info page the Current Version #, a msg that says if you are using the current version or not, and an Importrange formula.

K3 = 0.264  
L3 = =IF(M3>K3,HYPERLINK("https://docs.google.com/spreadsheets/d/(sheet ID here)/","Minor fixes/corrections available on the SOURCE SPREADSHEET"),"Your copy is up-to-date")  
M3 = =IMPORTRANGE("sheet ID here","Info!K3")

BTW, Importrange is slow as HECK!! Takes forever for the change to register.
When someone makes a copy of my sheet so that they can input their own data, since mine is obviously locked or people would just go around screwing it up, when I change the Version # in K3, it will, slowly (oh God so sloooooowly) and eventually, let the copy know that there is a change on my original one. BUT they will only know if they are on the Info tab, and not one of the other tabs.

It also, thanks to a conditional, will change J3 to Red or Green based on if it out of date or not.

I've toyed with the idea of adding L3's msg on every sheet but honestly I'm a minimalist and keep as few cells viewable as possible. I'll put at most 2 cells across and down for spacing and that's it. So, I'd like to not have to add extra columns just to put the alert on them. Obviously, I can change the msg and just say "Out of Date" and there not be any "Up to Date" text.

=IF(Info!M3>Info!K3,HYPERLINK("https://docs.google.com/spreadsheets/d/(sheet ID here)/","Out of date"),"")

And yeah, I know, that WOULD fit on 1 cell, and I will most likely just do that. But I'm also really wondering if there's another way to do that with like making the Info tab change color from Green to Red if there is a change to the version #.

The tab isn't colored right now, so something that will make it green or red is what I was looking for.

I found an app-script here: https://productforums.google.com/forum/#!msg/docs/R1CANl8LW80/2jRluKIoBgAJ
And while it works on his linked sheet, I can't get it to work on mine for some reason. I've changed the row/column to L3 and changed the colors. Also, when I click Run in the script editor itself, it throws an undefined error. But like I said, it works perfectly fine on his sheet AND it also throws the same error. I'd made a copy and changed the trigger wording to mine and it also works. I did that in case it was a "because of spaces you need to use a double quote" thing. It worked right off the bat.

Admittedly, L3 IS a merged cell with L3 to L5 so I don't know if that's part of the issue, but I tested it by making it unmerged and it still won't work. I'm thinking that it's because L3 has a whole formula thing and that script is just for a normal typed in thing.

So, I'm clueless as to why it's not working, but I'm about 99% sure it's because L3 is a formula.

So, in a nutshell, (I know! Finally I get to my question!) anyone know of a way to make the tabs change color based on if it's a current version or not? Even if it's a formula in the cell? Or maybe make the tab color match L3's color which almost sounds easier?

And is there a faster way than Importdata to do that? Cause it's been over 30 minutes and my test sheet STILL hasn't been alerted that there's been an update.

If you are still reading, thanks. I warned you that it might be wordy. Hell, I'm reading it again and I think it's almost confusing :)

EDIT: Ok, so, something that I just noticed. For some reason one of my tabs, the 3rd one has the green color on it from outta no where. I am pretty sure that when I was writing up my post, at no point in time was that tab green. Of course, with 10 chrome tabs open trying to look up scripts as well as typing my post up, I could have missed it. I really don't know.
I also noticed that the script that I am using doesn't have an indicator of which sheet I want it to work on, so, I'm guessing it's just picked some random tab to color the default that I have which is green.

Also, here's the code that I am talking about from the link

function onEdit(e) {
  if(e.range.rowStart === 12 && e.range.columnStart === 3) {
    var color;
    switch(e.value) {
      case 'Your copy is up-to-date':
        color = 'green';
        break;
      case 'Minor fixes/corrections available on the SOURCE SPREADSHEET':
        color = 'red';
        break;
      default:
        color = 'green';
    }
    e.source.getActiveSheet().setTabColor(color);
  }
}

I changed it after a quick search to this, but it still doesn't work. Still has the 3rd tab colored

function onEdit(e) {
  if(e.range.rowStart === 12 && e.range.columnStart === 3) {
    var color;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var first = ss.getSheetByName("Info");
    switch(e.value) {
      case 'Your copy is up-to-date':
        color = 'green';
        break;
      case 'Minor fixes/corrections available on the SOURCE SPREADSHEET':
        color = 'red';
        break;
      default:
        color = 'green';
    }
    first.setTabColor(color);
  }
}

I'm sure I'm missing something because of that e thing in the 1st 2 lines.

0

Well....looks like I was able to find an answer!
https://stackoverflow.com/questions/43548735/google-sheet-script-tab-color
Modified to:

function onEdit(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var first = ss.getSheetByName("Info");
 var cell1 = first.getRange("L3");
 var cellContent1 = cell1.getValue();
  if (cellContent1 == 'Minor fixes/corrections available on the SOURCE SPREADSHEET') {
 first.setTabColor("red");
  }
  if (cellContent1 == 'Your copy is up-to-date') {
 first.setTabColor("green");
  }
}

When I ran it, it went thru a whole permissions thing and even gave a warning that I needed to ignore before I could accept it. Is this going to be the case for everyone who makes a copy of this?
Slight side question, how would I publish this so that others can use it down the line? Don't know what to select as an option.

EDIT: Ok, so, yeah, looks like whomever makes a copy will need to run the script editor and run it and accept the script before it works. Kinda annoying :(
IS there another way that will run right out the box?
Or am I back to my original "Msg in every tab" idea? :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.