Writing apps scripts to copy and paste to hidden Google sheets

Question

I have a multi-sheet spreadsheet of which most of the sheets are 'work'-type sheets which the user has no need to view and, as such, are hidden. One of these sheets, however, is subject to a copy values only and a copy formulas only script operation. My investigations lead me to believe that, for either of these operations to succeed, the sheet must be unhidden and made the active sheet. However, the script/macro code stalls out. The relevant code is below:

/**
* rngPasteValus - paste values only in a specified range
*
* @param {A1Notation} rngA1 - specified range
*/

function rngPasteValus(rngA1) {
  var copyRng;

  SprdSht = SpreadsheetApp.getActiveSpreadsheet();  // global SprdSht
  copyRng = SprdSht.getRange(rngA1);      // identify the range
  Sht = copyRng.getSheet();     // identify its host sheet
  shtUnhide(Sht.getName());     // unhide the sheet
  Sht.setActiveRange(copyRng);  // establish the active range
  copyRng.copyTo(copyRng,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);  // copy all values eliminating any formulae
  shtHide();                    //hide the active sheet
}

/**
* rngPasteFormula  - paste a relocatable formula into al cells in a specified range
*
* @param {A1Notation} rngA1 - cell range
* @param {A1Notation} fRngA1 - cell host from which to acquire the relocatable formula
*/
function rngPasteFormula(rngA1, fRngA1) {
  var copyRng, formRng;

  SprdSht = SpreadsheetApp.getActiveSpreadsheet();  // establish addressability
  copyRng = SprdSht.getRange(rngA1);    // locate the (destination)range to which the formula will be copied
  formRng = SprdSht.getRange(fRngA1);   // locate the cell range holding the formula
  shtUnhide(formRng.getSheet().getSheetName());  //unhide the host sheet
  SprdSht.setActiveRange(formRng);      // activate the host range
  formRng.copyTo(copyRng, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);  // paste formula only to the destination
  shtHide();    // hide host sheet

}

/**
* shtHide - hide the current active sheet
*
*/

function shtHide() {
  SpreadsheetApp.getActive().getActiveSheet().hideSheet();  // hide the current active sheet
}

/**
* shtUnhide  unhide a named sheet
*
* @param {string} shtName - name of subject sheet
*/

function shtUnhide(shtName) {
  var namedSht;  // sheet object

  namedSht = SpreadsheetApp.getActive().getSheetByName(shtName);    // identify the named sheet 
  namedSht.showSheet(); namedSht.activate();   // show and activate the named sheet
  SpreadsheetApp.setActiveSheet(namedSht, false);    // establish the sheet as the active sheet
}

If you're pasting values only, then just use setValues and getValues, rather than activating a range and pasting from it — tehhowch

Cooper Cooper · Accepted Answer · 2019-09-01T04:43:07

This function will copy values and formulas from Sheet1 to Sheet2 and having either, both or neither hidden has no effect on the process.

function copyRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var fA=rg.getFormulas();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      if(fA[i][j]) {
        vA[i][j]=fA[i][j];
      }
    }
  }
  ss.getSheetByName('Sheet2').getRange(1,1,vA.length,vA[0].length).setValues(vA);
}

Writing apps scripts to copy and paste to hidden Google sheets

1 Answers