Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to preserve borders, merged cells & formula when using SetValue() function in AppScript?

I have recently written a code that fetches a range of date & sets it on another sheet. It works fine, but the set data is a little hard to read because the table borders/alignment/formula are not preserved. Is there a way that this can be preserved?
The script:

function saved() 
{
  //Source link
  var ssraw = SpreadsheetApp.getActiveSpreadsheet();
  var sheetraw = ssraw.getSheetByName('Sheet1');
  var range = sheetraw.getRange('C3:I22');
  var data = range.getValues();
  var mat = sheetraw.getRange('H3').getValue();
   if (mat==="RR No.:") //TR
    {
    var reqsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("by rail");
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).clear();
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).setValues(data);
    }
  else if (mat==="TR No.:") //RR
    {
    var reqsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("by road");
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).clear();
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).setValues(data);
    }
}

For example,
Sheet: https://docs.google.com/spreadsheets/d/1ibdZSYyMbVSp-Y3Ibuqzfc8aYZV6ZkdJqJfrnsVX9Ik/edit#gid=0
The target range in sheet1 has borders & formulae, but after running the above script ("save"), the saved range has neither borders nor formulae.
Is there a way that can be achieved?

Thanks

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

When I saw your sample Spreadsheet, unfortunately, I couldn’t find the merged cells. But, if you want to keep the cell format when the values are copied, how about the following modification?

In this modification, the values and format are copied using copyTo.

Modified script:

function save() {
  var obj = {
    "RR": "by rail",
    "TR": "by road",
  };
  var ssraw = SpreadsheetApp.getActiveSpreadsheet();
  var sheetraw = ssraw.getSheetByName('Sheet1');
  var range = sheetraw.getRange('A2:C4');
  var mat = sheetraw.getRange('A1').getValue();
  if (!obj[mat]) return;
  var reqsheet = ssraw.getSheetByName(obj[mat]);
  reqsheet.getRange(reqsheet.getLastRow() + 2, 1, 20, 7).clear();
  var dstRange = reqsheet.getRange(reqsheet.getLastRow() + 2, 1, 20, 7);
  range.copyTo(dstRange);
  // dstRange.setBackground(null); // If you want to clear the background color of cells. Please use this line.
}
  • When this script is run, the cell values are copied by including the cell format in the destination sheet.

  • If you want to clear the background color of cells. Please use dstRange.setBackground(null);.

Reference:

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading