Google Apps Script Copy Range From One SpreadSheet To Another With Formats / Formulas

Question

This code works well, but I am having difficulty determining how to copy the formats as well as formulas along with the data. I would value any help, please.

  var MyWBA = SpreadsheetApp.openById('1234567');
  var MySHA = MyWBA.getSheetByName('Expense');
  var SRange = MySHA.getDataRange();
  var A1Range = SRange.getA1Notation();
  var SData = SRange.getValues();

  var MyWBB = 
  SpreadsheetApp.openById('122454');
  var MySHB = MyWBB.getSheetByName('Income');
  MySHB.getRange(A1Range).clearContent()
  MySHB.getRange(A1Range).setValues(SData);

Tanaike · Accepted Answer · 2017-07-18T07:40:11.300

1

How about this sample? This sample copies value, formula and format for each cell from the source spreadsheet, and paste them to the destination spreadsheet.

var srcID = "### Source Spreadsheet ID ###";
var src_sheetName = "### Source sheet name ###";

var dstID = "### Destination Spreadsheet ID ###";
var dst_sheetName = "### Destination sheet name ###";

var src_ss = SpreadsheetApp.openById(srcID);
var src = src_ss.getSheetByName(src_sheetName);
var srcrange = src.getDataRange();
var srcrangeA1 = srcrange.getA1Notation();
var src_values = srcrange.getValues();
var src_formulas = srcrange.getFormulas();
var src_formats = srcrange.getNumberFormats();
for (var i=0; i<src_formulas.length; i++) {
  for (var j=0; j<src_formulas[i].length; j++) {
    if (src_formulas[i][j]) {
      src_values[i][j] = src_formulas[i][j];
    }
  }
}
var dst_ss = SpreadsheetApp.openById(dstID);
var dst = dst_ss.getSheetByName(dst_sheetName);
dst.getRange(srcrangeA1).setValues(src_values);
dst.getRange(srcrangeA1).setNumberFormats(src_formats);

edited Jul 18 '17 at 07:40

answered Jul 18 '17 at 04:54

Tanaike

181,128
11
97
165

Thank You, Tanaike. In my research prior to posting, I learned the copyTo method only works if the ranges are in the same spreadsheet, not two different spreadsheets. When I run the code with copyTo, I receive the error "Target Range and Source Range must be in the same Spreadsheet." – Tacitus Jul 18 '17 at 05:48
@Tacitus I'm sorry. I misunderstand your question. I updated my answer, just now. Please confirm it. – Tanaike Jul 18 '17 at 06:11
Thank You, Tanaike. Before I proceed, may I ask, will all the users of the spreadsheet be required to enable Google Sheet API v4? My fear is that there are dozens of persons who will access this spreadsheet and I do not wish for them to have to be concerned with this process . . . – Tacitus Jul 18 '17 at 06:21
@Tacitus Can I ask you about the situation you use this script? I couldn't understand about it from your question. I'm sorry for my poor English skill. – Tanaike Jul 18 '17 at 06:30
Tanaike - if I may comment, I noticed no absence of skill, at all, in your English - your writing is coherent and articulate :) I have two spreadsheets, one serves as a Dashboard. The other Spreadsheet is updated with new data each week. I would like the user to be able to click a button in the destination sheet, and copy the new data from the source spreadsheet. I have researched for a number of hours for a script I could understand - but I have failed to locate a code. – Tacitus Jul 18 '17 at 06:36
@Tacitus Thank you for your concern. If you don't want to use Sheet API v4, it is necessary to copy each data in each cell. So can I ask you about data that you want to copy? About the each cell, there are the value, formula, colors of cell and fonts and so on. – Tanaike Jul 18 '17 at 06:40
Tanaike - the data is arranged in columns. There are columns with date formats, standard currency formats, text formats, and general number formats (not currency). Have I understood your question? The formulas are simple mathematical operatives such as addition / subtraction. – Tacitus Jul 18 '17 at 06:43
@Tacitus Updated my answer. Please confirm it. This sample copies value, formula and format for each source cell to destination ones. Sheet API v4 is not used for this. – Tanaike Jul 18 '17 at 07:41
Thank You, Tanaike! I will study and apply the code - my gratitude for your help, once more! – Tacitus Jul 18 '17 at 19:16
@Tacitus Welcome. Thank you, too. – Tanaike Jul 18 '17 at 22:20
@Tanaike - can the code be expanded to include display formats, data validations, borders, fonts, colors etc – arul selvan Aug 11 '22 at 09:22
@arul selvan About `can the code be expanded to include display formats, data validations, borders, fonts, colors etc`, I think that it's yes. If you want a sample script, I would like to support you. But, in that case, please post it as a new question. Because when your initial question is changed by comment, other users who see your question are confused. By posting it as a new question, users including me can think of it. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question? – Tanaike Aug 11 '22 at 23:10
Sure. You are an Angel:) – arul selvan Aug 12 '22 at 08:55
https://stackoverflow.com/questions/73331715/copy-a-range-to-another-spreadsheet-with-data-validations-formats-etc – arul selvan Aug 12 '22 at 09:04
That's the new question @Tanaike – arul selvan Aug 12 '22 at 09:04
@arul selvan Thank you for your response. Now, when I saw your new question, I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. – Tanaike Aug 13 '22 at 00:46
@Tanaike , That answer is not correct. It does not solve my issue. That solution copies within the same spreadsheet. What I want is to copy in ANOTHER spreadsheet. It will be great to get help on it – arul selvan Aug 17 '22 at 08:41
@Tanaike, I got some help from https://stackoverflow.com/questions/25106580/copy-value-and-format-from-a-sheet-to-a-new-google-spreadsheet-document and got most of the things I wanted. The Justification is not getting copied. – arul selvan Aug 17 '22 at 09:50
@arul selvan About `That answer is not correct. It does not solve my issue.`, I apologize for this. – Tanaike Aug 17 '22 at 11:16
@Tanaike, why should you apologize? That answer was by some one else, not yours. – arul selvan Aug 19 '22 at 12:38
@arul selvan Thank you for replying. About `That answer was by some one else, not yours.`, it's yes. But, I understood that it was not useful, I thought that I would like to apologize as one of the answerers. – Tanaike Aug 19 '22 at 12:46

Google Apps Script Copy Range From One SpreadSheet To Another With Formats / Formulas

1 Answers1

Linked