Sync the first column(s) and row(s) of sheets in the same Google Sheets file

1
votes

I need to sync all sheets in the same Google Sheets file. I only want to sync the first row and the first column (which are frozen), while the rest of the contents should not be synced.

I'd also like to make it so that if I insert/remove a row to/from a sheet, the same action is performed to all other synced sheets too. Also, if other modifications are done, e.g. a row is frozen, the same should be done on all sheets.

I have seen the codes in these questions:

In short, I want to be able to use a "master" sheet form, and have several sheets with different values in them. I a change to the master is applied, changes to the sheets should reflect the changes in the master. Two way sync would be nice but is not a stringent requirement.

So far I have managed to put down this:

var masterSheetN = 2; /*2 means the master sheet is the second sheet. Sheets before the master sheet are ignored*/

function importData() {  
  /*Input sheet*/
  var fromSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var fromSheets = fromSpreadsheet.getSheets();
  var fromWorksheet = fromSheets[masterSheetN-1];
  var fromData = fromWorksheet.getDataRange();
  var fromRowsN = fromData.getNumRows()
  var fromColsN = fromData.getNumColumns()
  var fromRow1 = fromWorksheet.getRange(1, 1, 1, fromColsN);
  var fromCol1 = fromWorksheet.getRange(1, 1, fromRowsN);

  for (i=masterSheetN; i<fromSheets.length; i++) {
    /*Output sheet*/
    var toWorksheet = fromSheets[i];
    var toRow1   = toWorksheet.getRange(1, 1, 1, fromColsN);
    var toCol1   = toWorksheet.getRange(1, 1, fromRowsN);

    /*Sync row and col 1*/
    toRow1.setValues(fromRow1.getValues());
    toCol1.setValues(fromCol1.getValues());

    /*Sync format for the whole sheet*/
    var toGID = toRow1.getGridId()
    fromData.copyFormatToRange(toGID, 1, fromColsN, 1, fromRowsN)
  }
}

This successfully syncs all sheets with the master sheet for the first row and column, and also for formatting. However, it is quite slow, especially for syncing the formatting (why? syncing the first row/column seems much faster), and it does not sync things like frozen/protected rows, etc. Is there a way to sync everything from the Master sheet when a new sheet is created?

1
google-apps-scriptgoogle-sheets
Is the syncing one way or two way? How many people can edit? This suggested answer on your second linked post should give some guidance on how to set this up effectively. – Brian
@Rubén Hi, I will edit the question tomorrow. Yes I can use formulas, but it only copies the content. What I want is a master form to be filled with different data, similar in each sheet. This means that if I change the (e.g.) conditional formatting in the "Master sheet" it should be changed in all sheets altogether. I do not know if this is possible. – AF7
importrange() will only show the data, not the formatting. It is used to bring data from one file into a second file. The file_key is the long string in the URL, from one slash to another as shown here: docs.google.com/spreadsheets/d/___file_ key_is_here___/edit#gid=0 The range can be open. The following will bring in all items in columns A and B =IMPORTRANGE(file_key, "sheet1!A1:B") You will only be able to edit this part in the main sheet and you do NOT want to re-sort the data or insert lines unless your other sheets only use functions on the initial data, else items will not line up. – Karl_S
@Rubén I have edited my question with the code I have come up so far, thanks also to your inputs. It seems a bit slow tho, and it does not sync everything (only format and first row/col). How can I fix this? – AF7

1 Answers

0
votes

Partial answer

Instead of getValues(), setValues() and copyFormatToRange() use copyTo(Range).

Notes

I think that it only will copy values and format. Perhaps you should also use getDataValidation() / setDataValidation(rule) to sync the data validation rules.

See also