N
N
NewUserExel2020-05-04 18:42:24
Google Sheets
NewUserExel, 2020-05-04 18:42:24

How to completely copy a cell from one sheet to another?

Hello everyone, I have never worked with tables, so I ask you to give the most complete answer.
In fact, I have 3 sheets, the first and second with data, and 3 are final, on the third I copied the cells from 1 and 2 with the formula = sheet name! A1
but this formula copies only the text, and the whole point is that I constantly edit the color cells on sheets 1 and 2.
How to make cells fully interconnected so that any change is displayed on sheet 3

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
Grigory Boev, 2020-05-04
@ProgrammerForever

I would do like this:

/**
 * Копирует значения и форматирование из fromRange в toRange
 *
 * @param {"A1:B10"} fromRange Исходный массив
 * @param {"D1:E10"} toRange Конечный массив
 * @return 0 если выполнилось без ошибок, или описание ошибки
 * @customfunction
 */
function copyRange(fromRange, toRange){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  try{
    var source = ss.getRange(fromRange);
    var dest   = ss.getRange(toRange);
    
    dest.setValues(source.getValues());
    dest.setBackgrounds(source.getBackgrounds());
    // и т.д. Есть много чего из форматирования, получается по get... ставится по set...
    
    return 0;//Завершение без ошибок
  }catch(err){
    Logger.log(Utilities.formatString((arguments.callee.toString().match(/function ([^(]*)\(/)[1]) + "(%s) - %s", Array.from(arguments).join(", "), err.message)); //Ошибку в лог
    return err.message;//Завершение с ошибкой
  };
};

Use like this:
function syncRanges(){
  copyRange("b7:b11", "c7:c11"); // b7:b11 >> c7:c11
  copyRange("f1:f20", "g1:g20"); // f1:f20 >> g1:g20
  //и т.п. можно вызывать несколько раз для разных диапазонов
};

And set up a trigger for the syncRanges () function "to change the table" or "by time".

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question