Google-sheets – How to Copy Cell Value and Format From One Tab to Another Using a Formula

formulasgoogle sheetsgoogle-apps-script

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.

Best Answer

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

Related Topic