Copy from template to rangelist-CodePudding

function FunctionC12C31() {
  var allSheetTabs,i,L,thisSheet,thisSheetName,sheetsToExclude,value;
  sheetsToExclude = ['Template','Sheet1','Sheet2'];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  allSheetTabs = ss.getSheets();
  L = allSheetTabs.length;
  for (i=0;i<L;i  ) {
  thisSheet = allSheetTabs[i];
  thisSheetName = thisSheet.getName();
  //continue to loop if this sheet is one to exclude
  if (sheetsToExclude.indexOf(thisSheetName) !== -1) {continue;}
  value = thisSheet.getRangeList(['C12:C35','C5:C6','G16:G19','G4']).clearContent();
}}

Need help modifying this to instead of clearing contents it will copy the formula from a RangeList(['C12:C35','C5:C6','G16:G19','G4']) to be copied to from a Template Sheet too all sheet except to "sheetsToExclude" list.

or perhaps a different script?

copy from a template sheet RangeList(['C12:C35','C5:C6','G16:G19','G4'])
data will be formulas and need to be pasted on same RangeList
paste data to ALL sheets except from specified sheets. Like sheetsToExclude = ['Template','Sheet1','Sheet2'];

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the formulas from the cells 'C12:C35','C5:C6','G16:G19','G4' of a template sheet. And, you want to put the retrieved formulas to the same ranges in the sheets except for sheetsToExclude = ['Template','Sheet1','Sheet2'] in the active Spreadsheet.
The template sheet is included in the same active Spreadsheet. The sheet name is "Template".

In this case, how about the following modification?

Modified script 1:

Unfortunately, in the current stage, the values cannot be retrieved and put from the discrete cells using RangeList. So, in this case, it is required to use another method. In order to retrieve the formulas from the discrete cells and put the formulas to the discrete cells with the row process cost, I would like to propose using Sheets API. So, before you use this script, please enable Sheets API at Advanced Google services.

In this script, "Method: spreadsheets.batchUpdate" is used.

function myFunction() {
  // These variables are from your showing script.
  var templateSheetName = "Template";
  var sheetsToExclude = [templateSheetName, 'Sheet1', 'Sheet2'];
  var ranges = ['C12:C35', 'C5:C6', 'G16:G19', 'G4'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var template = ss.getSheetByName(templateSheetName);
  var sheetId = template.getSheetId();
  var gridRanges = ranges.map(r => {
    var range = template.getRange(r);
    var row = range.getRow();
    var col = range.getColumn();
    return {
      sheetId,
      startRowIndex: row - 1,
      endRowIndex: row - 1   range.getNumRows(),
      startColumnIndex: col - 1,
      endColumnIndex: col - 1   range.getNumColumns(),
    };
  });
  var requests = ss.getSheets().reduce((ar, s) => {
    if (!sheetsToExclude.includes(s.getSheetName())) {
      gridRanges.forEach(source => {
        var destination = JSON.parse(JSON.stringify(source));
        destination.sheetId = s.getSheetId();
        ar.push({ copyPaste: { source, destination, pasteType: "PASTE_FORMULA" } });
      });
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

When this script is run, the formulas are retrieved from 'C12:C35', 'C5:C6', 'G16:G19', 'G4' of templateSheetName sheet, and the retrieved formulas are put into the same ranges in the sheets except for sheetsToExclude.

Modified script 2:

In this script, "Method: spreadsheets.values.batchGet" and "Method: spreadsheets.values.batchUpdate" are used.

function myFunction() {
  // These variables are from your showing script.
  var templateSheetName = "Template";
  var sheetsToExclude = [templateSheetName, 'Sheet1', 'Sheet2'];
  var ranges = ['C12:C35', 'C5:C6', 'G16:G19', 'G4'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheets = ss.getSheets().filter(s => !sheetsToExclude.includes(s.getSheetName()));
  var formulas = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: ranges.map(r => `'${templateSheetName}'!${r}`), valueRenderOption: "FORMULA" }).valueRanges;
  var data = sheets.flatMap(s => formulas.slice().map(({ range, values }) => ({ range: range.replace(templateSheetName, s.getSheetName()), values })));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ssId);
}

When this script is run, the formulas are retrieved from 'C12:C35', 'C5:C6', 'G16:G19', 'G4' of templateSheetName sheet, and the retrieved formulas are put into the same ranges in the sheets except for sheetsToExclude.

Note:

If you cannot use Sheets API, how about the following modified script? In this case, only the Spreadsheet service (SpreadsheetApp) is used.

  function myFunction2() {
    // These variables are from your showing script.
    var templateSheetName = "Template";
    var sheetsToExclude = [templateSheetName, 'Sheet1', 'Sheet2'];
    var ranges = ['C12:C35', 'C5:C6', 'G16:G19', 'G4'];

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ssId = ss.getId();
    var sheets = ss.getSheets().filter(s => !sheetsToExclude.includes(s.getSheetName()));
    var formulas = ranges.map(r => ss.getSheetByName(templateSheetName).getRange(r).getFormulas());
    sheets.forEach(s => ranges.forEach((r, i) => s.getRange(r).setFormulas(formulas[i])));
  }

Modified script 1:

Modified script 2:

Note:

References: