setFormulas and rangeList

Question

I'm trying to setFormulas over a range of non-contiguous cells. I need a formula (they're all different) set every 30 cells in a single column (c).

It works to setFormula for each cell, but creating 56 variables seems unnecessary. I can get the formulas but not set them as intended. I also tried using getRangeList but I'm not sure that does what I think it does. Any advice?

function test() {
  var spreadsheetU09U10 = SpreadsheetApp.openById('some url');
  var sheetU09 = spreadsheetU09U10.getSheetByName('TEST');
  var sheetU10 = spreadsheetU09U10.getSheetByName('U10');
  var sheetDATA = spreadsheetU09U10.getSheetByName('Sheet4');

  //U09 SHEET
  //var rangeListU09 = sheetU09.getRangeList(['C4','C34','C64','C94','C124','C154','C184','C204','C234','C264','C294','C324','C354','C384','C404','C434','C464','C494',
  //'C524','C554','C584','C604','C634','C664','C694','C724','C754','C784']);
  //Logger.log(rangeListU09);
  var startRow = 4;
  var startColumn = 3;
  var numRows = sheetU09.getLastRow();
  var numColumns = 1;
  var range = sheetU09.getRange(startRow, startColumn, numRows, numColumns);

  var getFormulasU09 = sheetDATA.getRange('C30:C57').getFormulas();
  //Logger.log(getFormulasU09);
  Logger.log(getFormulasU09.length);

  for (var i = 0; i < getFormulasU09.length; i++) {
    var setFormulasU09 = range.setFormulas(getFormulasU09);
    Logger.log(setFormulasU09);
    startRow = startRow + 29; 
  }

score 3 · Accepted Answer · answered Dec 25 '18 at 04:57

You want to put formulas to the individual cells.
- You want to put 28 formulas to cells of ['C4','C34','C64','C94','C124','C154','C184','C204','C234','C264','C294','C324','C354','C384','C404','C434','C464','C494', 'C524','C554','C584','C604','C634','C664','C694','C724','C754','C784'] in the sheet of TEST.

If my understanding is correct, how about using values.batchUpdate of Sheets API? The flow of this script is as follows.

Set range list as 1 dimensional array.
Retrieve formulas.
Create request body for sheets.spreadsheets.values.batchUpdate.

In order to use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Sample script:

function test() {
  var spreadsheetId = "### spreadsheetId ###"; // Please set this.

  var sheetName = "TEST";
  var spreadsheetU09U10 = SpreadsheetApp.openById(spreadsheetId);
  var sheetU09 = spreadsheetU09U10.getSheetByName(sheetName);
//  var sheetU10 = spreadsheetU09U10.getSheetByName('U10'); // This is not used in this script.
  var sheetDATA = spreadsheetU09U10.getSheetByName('Sheet4');

  var rangeListU09 = ['C4','C34','C64','C94','C124','C154','C184','C204','C234','C264','C294','C324','C354','C384','C404','C434','C464','C494', 'C524','C554','C584','C604','C634','C664','C694','C724','C754','C784'];
  var getFormulasU09 = sheetDATA.getRange('C30:C57').getFormulas();

  rangeListU09 = rangeListU09.map(function(e) {return sheetName + "!" + e});
  var resource = {
    data: rangeListU09.map(function(e, i) {return {range: e, values: [[getFormulasU09[i][0]]]}}),
    valueInputOption: "USER_ENTERED",
  };
  Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
}

Note:

From your question, I'm not sure about the detail formulas. If the a1Notation of each formulas is required to be modified, can you provide a sample spreadsheet including the formulas?

Reference:

sheets.spreadsheets.values.batchUpdate

If I misunderstand your question, please tell me. I would like to modify it.

That worked well. I'm not 100% sure what you did, but it worked and gives me something to tinker with. — N.O.Davis, Dec 25 '18 at 14:26
@Nate Davis I'm glad your issue was resolved. Thank you, too. — Tanaike, Dec 25 '18 at 22:57

tehhowch · Answer 2 · 2018-12-25T13:34:12.327

It isn't clear exactly where the formulas you are using are originating from, but the RangeList class can help reduce the read time, even if you use it just to call getRanges. If the formula is the same in R1C1 format, then you can very effectively use RangeList#setFormulaR1C1.

Assuming you have formulas in one region that must be written verbatim in a disjoint set of cells:

const wb = SpreadsheetApp.getActive();
// Assuming only text formulas, not actual "entered" formulas
const formulas = wb.getSheetByName("formulas").getDataRange()
    .getValues()
    .map(function (row) { return row[0]; });

const sheet = wb.getSheetByName("some name");
const destinations = [
  // Depending on the relationship between destinations, one could programmatically generate these
];
// Efficiently acquire references to multiple disjoint Ranges
const rl = sheet.getRangeList(destinations);
// Assume the i-th formula goes in the i-th range
rl.getRanges().forEach(function (rg, i) {
  rg.setFormula(formulas[i]);
});
// The RangeList makes uniformly formatting these disjoint ranges extremely simple
rl.setFontWeight('bold');
...

Reference - RangeList

These are actual "entered" formulas which is why I was using `getFormulas`. Would If I use a text only version, would I then need to go through and plug in the `=` to make them all active? — N.O.Davis, Dec 25 '18 at 14:11
You can replace the `getValues` with `getFormulas` for equivalency. I believe the equals sign is added if it is not present when using the `setFormula` variants. — tehhowch, Dec 25 '18 at 14:44

Cooper · Answer 3 · 2018-12-25T16:34:48.377

I'm assuming that you want to copy the whole column starting from the cell locations in the array. That wasn't really clear to me.

function test109() {
  var ss=SpreadsheetApp.getActive();
  var shU09=ss.getSheetByName('35');//formulas get copied into here starting at row 4
  var shDATA=ss.getSheetByName('36');//formulas stored in here C30:C57
  var fA=shDATA.getRange('C30:C57').getFormulas();
  var dA=['C4','C34','C64','C94','C124','C154','C184','C204','C234','C264','C294','C324','C354','C384','C404','C434','C464','C494','C524','C554','C584','C604','C634','C664','C694','C724','C754','C784'];  
  for(var i=0;i<dA.length;i++){
    var rgs=Utilities.formatString('%s:%s',dA[i],shU09.getRange(dA[i]).offset(fA.length-1,0).getA1Notation());//this uses range.offset to calculate the correct range in A1Notation.
    shU09.getRange(rgs).setFormulas(fA);
  }
}

As it turns out I just noticed that there are 28 locations and 28 formulas. Perhaps that was intentional and you want to copy a different formula in each location then this version would do that.

function test109() {
  var ss=SpreadsheetApp.getActive();
  var shU09=ss.getSheetByName('35');//formulas get copied into here starting at row 4
  var shDATA=ss.getSheetByName('36');//formulas stored in here C30:C57
  var fA=shDATA.getRange('C30:C57').getFormulas();
  var dA=['C4','C34','C64','C94','C124','C154','C184','C204','C234','C264','C294','C324','C354','C384','C404','C434','C464','C494','C524','C554','C584','C604','C634','C664','C694','C724','C754','C784'];
  for(var i=0;i<dA.length;i++){
    shU09.getRange(dA[i]).setFormula(fA[i][0]);
  }
}

Range Offset

I kept getting the error "Cannot Convert Array to Object[][] " — N.O.Davis, Dec 25 '18 at 14:07
Just needed a minor tweak. Sorry it was late last night when I did this. Just had to change `shU09.getRange(dA[i]).setFormulas(fA[i])` to `shU09.getRange(dA[i]).setFormula(fA[i][0])` — Cooper, Dec 25 '18 at 16:36

setFormulas and rangeList

3 Answers3

Sample script:

Note:

Reference:

Linked