RangecopyFormat (only) from one spreadsheet to another with google script

0
votes

I have been searching for days to accomplish this without success. I have multiple users with different spreadsheets that look the same but have different ID's and security. If I make a change in format to my base sheet I need to pull the new formats from there and distribute to all users sheets so I am consistent across the board.

*Do not want to copy values *Do not want to copy sheet to 'Copied Sheet' and then delete and rename sheet

3
google-apps-script

3 Answers

3
votes

Possible Solution:

You can use the Sheets API to get the format from one Spreadsheet and update another Spreadsheet.

Flow:

  • Use Spreadsheets.get to get source format
  • Use updateCells batchUpdate request to update another spreadsheet with source format

Sample Script:

function copyFormatFromSpreadsheetToSpreadsheet() {
  var sourceSsId = SpreadsheetApp.getActiveSpreadsheet().getId(),
    destSsId = '##destSpreadsheetIdHere##',
    sourceRng = 'Sheet1!A1:B4',
    destRng = {
      //Must be of same dimensions as sourceRng
      //Represents Sheet1!B1:C4
      sheetId: 123456789, //use 9 digit GridId
      startRowIndex: 0,
      endRowIndex: 4, //end exclusive
      startColumnIndex: 1,
      endColumnIndex: 3,
    };

  /*Get Format of Source Spreadsheet*/
  var rowData = Sheets.Spreadsheets.get(sourceSsId, {
    ranges: sourceRng,
    fields: 'sheets.data.rowData.values.userEnteredFormat',
  }).sheets[0].data[0].rowData;

  /*Copy Format to Destination Spreadsheet*/
  var request = {
    requests: [
      {
        updateCells: {
          rows: rowData,
          fields: 'userEnteredFormat',
          range: destRng,
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(request, destSsId);
}

References:

2
votes

Sample with "copyFormatToRange":

var config = {
  // range to modify
  range: "A1:C3",
  // list of spreadsheets/sheets to modify
  spreadSheets: [
    {
      // from spreadsheets url https://docs.google.com/spreadsheets/d/XXX/edit#gid=YYY
      spreadSheetId: 'XXX',
      sheetId: YYY // always "0" at the creation of the first (spread)sheet
    }
  ]
};

// update spreadsheets
function updateSpreadSheets() {
  for (var i = 0, j = config.spreadSheets.length; i < j; i++) {

    // get the spreadsheet to modify
    var spreadsheet = SpreadsheetApp.openById(config.spreadSheets[i].spreadSheetId);
    // get the sheet to modify
    var sheet = spreadsheet.getSheets().filter(function(s) { return s.getSheetId() === config.spreadSheets[i].sheetId; })[0];

    // copy from the original sheet into the spreadsheet to modify
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().copyTo(spreadsheet);

    // get the new tmp sheet
    var sheetTmp = spreadsheet.getSheets()[spreadsheet.getNumSheets()-1];
    // get the range from the tmp sheet
    var sheetTmpRange = sheetTmp.getRange(config.range);

    // copy the format from the tmp sheet to the sheet to modify
    sheetTmpRange.copyFormatToRange(sheet, 1, 1, 1, 1);

    // remove the tmp sheet
    spreadsheet.deleteSheet(sheetTmp);
  }
}

Plus, if you need these functions to retrieve ids:

// get spreadsheet id
function getSpreadSheetId() {
 Logger.log(SpreadsheetApp.getActiveSpreadsheet().getId());
}

// get spreadsheet sheet id
function getSheetId() {
  Logger.log(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId());
}
1
votes

use getBackgrounds() and setBackgrounds() of the Range Object to first copy of the cell background and then set it to the necessary range in any spreadsheet. Similarly use getFontColors() and setFontColors() for changing the font colors. You'll find these and other similar funtions on this page. https://developers.google.com/apps-script/reference/spreadsheet/range#setfontsizesize