0

An answer was provided in this question: webapps: Cell reference with colour formatting for doing so within a tab, but not across tabs. Does anyone have suggestions for a script that will work across tabs?

Asking for a teacher friend that wants to combine attendance/roster from multiple tabs (classes) into one master tab.

I have created a sample google sheet that shows how I am able to reference cells from other tabs in the "Master" tab with a simple "=" formula, here: https://docs.google.com/spreadsheets/d/1nMvs5-PWenGcOCNnA9rKnhGo-yAjjLNY07dhoR6-fCw/edit?usp=sharing.

The goals is that when I link to a cell from the other tab, the formatting of that cell should be retained. Particularly, this is helpful for teachers that highlight specific words in a student/participant's name when trying to remember that student's name. They are able to do so on individual rosters, but not on a comprehensive list by simply linking to that cell. They would have to instead copy/paste formatting from the individual tab each time, which can become cumbersome with greater number of classes.

  • Welcome. Would you please edit your question to explain your specific scenario. It's unhelpful to rely solely on a reference to another question, particularly when that question doesn't satisfy your requirements. Also, the referenced question has three answers - which of these do you want to implement? So far as "a script that will work across tabs", the likely main change is that the script will need to define both a target sheet/range as well as a source sheet/range but it's difficult to say how this could be done without knowing your specific situation. – Tedinoz Aug 18 at 23:36
  • Thank you for the suggestions. I have edited the question to provide an example problem and further description. I appreciate your assistance. – Izhaar S. Aug 19 at 18:14
  • FYI, I made a typo on earlier comment. I said "The addition of the links to different tabs is just a "simple" addition". What I mean to say was "The addition of the links to different tabs is NOT just a "simple" addition". That was at a stage when I was testing and trying to decide how best to deliver a solution (custom function), onedit, etc and I was trying to extra the sheet name from the formula, and use that it in the script. FWIW, as it turned out, in the submitted answer, using onEdit, the referencing of the class sheet was absurdly easy. – Tedinoz Aug 23 at 2:47
0

You have a series of Class sheets in which the student name may be formatted by the teacher. You also have a Master sheet where the names of the students in each class are linked by formula. Your goal is to apply any formatting from the Class sheet to the equivalent student/class on the Master sheet.

There are many ways in which your scenario might be answered. Please consider this as just one solution.

The referenced question used a custom formula to apply the formatting in a limited case scenario on a single sheet. In this case, given the number of classes and the number students, I was concerned about the possible overhead on the spreadsheet if the same approach (custom function) was used. In addition, the student name formatting is unlikely to change regularly throughout the day, so I judged that a immediate/real-time update was not essential.

On the Master, I also moved the student names down by one row so that their position aligned with the other sheets - all student names (on every sheet) now begin on row 5.

The script uses a simple onedit(e) trigger. I have added a checkbox on the Master sheet. When the user wishes to update the student name formats on the Master, simply check the checkbox. When the process is complete, the checkbox value reverts to unchecked/(blank), ready for the next update.


function onEdit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheetName = "Master"
  var masterSheet = ss.getSheetByName(masterSheetName);
  
  // get some parameters of the Master sheet
  var masterLR = masterSheet.getLastRow();
  var masterLC = masterSheet.getLastColumn();
  
  
  // establish variables
  // checkbox is trigger to update formats
  var checkboxLoc = "A2";
  var headerRows = 4;

  //  if checkbox is checked AND the sheet is the "Master" Sheet
  if (e.range.getA1Notation() == checkboxLoc && e.value == "TRUE" && e.source.getSheetName() ==  masterSheetName){  
    // IF is successfulk, do stuff
    // Logger.log("DEBUG: if is successful"); 

    // loop columns/classes
    for (var c = 0;c<masterLC;c++){
      
      // master range will progressively shift one column to the right.
      // to cater for each cvl;ass
      var masterRange = masterSheet.getRange(headerRows+1, c+1, masterLR-headerRows, 1)
      // Logger.log("DEBUG: the master range is "+masterRange.getA1Notation());
     
      // convert column number to string
      var classN = c+1;
      var classNum = classN.toString();
      // define the class sheet
      var classSheet = ss.getSheetByName(classNum);
      // define the range on the class
      var classRange = classSheet.getRange(headerRows+1, 1, masterLR-headerRows, 1)

      // following borrowed from 
      // Cell reference with colour formatting
      // https://webapps.stackexchange.com/q/50409/196152
      // get the formats
      var formats = {
        fontColors: classRange.getFontColors(),
        backgrounds: classRange.getBackgrounds(),
        fonts: classRange.getFontFamilies(),
        fontWeights: classRange.getFontWeights(),
        fontStyles: classRange.getFontStyles(),
        verticalAlignments: classRange.getVerticalAlignments(),
        horizontalAlignments: classRange.getHorizontalAlignments(),
        numberFormats: classRange.getNumberFormats()
      };
      
      // apply the formats to the master sheet
      masterRange.setBackgrounds(formats.backgrounds);
      masterRange.setFontColors(formats.fontColors);
      masterRange.setFontFamilies(formats.fonts);
      masterRange.setFontWeights(formats.fontWeights);
      masterRange.setFontStyles(formats.fontStyles); 
      masterRange.setVerticalAlignments(formats.verticalAlignments);
      masterRange.setHorizontalAlignments(formats.horizontalAlignments);
      masterRange.setNumberFormats(formats.numberFormats);
    
    }
    
  }else
  {
    // if statement failed, do nothing
    // Logger.log("DEBUG: if failed");
     
  }

  // set the checkbox status back to unchecked
  masterSheet.getRange(checkboxLoc).setValue("FALSE");
  
  return;
}

Before

Before


After

After

| improve this answer | |
  • FWIW, I spent an inordinate amount of time trying, without success, to develop a formula to do this. Probably someone like @player0 could do it in their sleep, but it confounded me and the script was my best option. That said, I am concerned about the overhead and whether there is a "need" for a real-time update of formatting. – Tedinoz Aug 24 at 21:50

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.