Search code examples
google-apps-scriptgoogle-sheetsrangegoogle-sheets-apisetvalue

How can I set new values in a range list, from the sums in another range list?

Please excuse my limited knowledge in advance.

In Google Sheets, I have a budget column (K) that sums multiple inputs and displays the most recent totals.

I am trying to create a script that will copy select values from column K and set those values into column I upon execution.

Both column K and I have sum totals in various rows throughout so a simple copy and pasting of the entire column would overwrite those functions. This will be a repeatable action to set new values once they are calculated into this column so I plan to assign it to a button.

I'm using User Tanaike's wonderful RangeListApp to help reference the multiple ranges I'm referencing.

The first half of the code seems to be working:

function UpdateEFC() {
  var rangeList = ['K4:K6', 'K9:K14', 'K17:K18', 'K21:K24', 'K27', 'K30:K38', 'K41:K49', 'K52:K53', 'K56:K60', 'K63:K67', 'K70:K72', 'K75:K76', 'K79:K85', 'K88:K92', 'K95:K106', 'K109:K114', 'K117:K126', 'K129:K133', 'K136:K147', 'K150:K153', 'K156:K163', 'K166:K167', 'K170:K174', 'K177', 'K180:K182', 'K185', 'K188:K190', 'K193', 'K196:K205', 'K208:K214', 'K217'];
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).getDisplayValues();

Again, each range listed (i.e. "k4:k6") are sum functions in each cell. When I log this script it returns the calculated value in each cell. Here is an example from the log:

[{range='CR Template'!K4:K6, values=[[ $ 28,000.00 ], [ $ 25,000.00 ], [ $ 27,500.00 ]]},

When I attempt to set these values into column I, however, I run into issues.

I have tried the following:

var destinationList = ["I4:I6", "I9:I14", "I17:I18", "I21:I24", "I27", "I30:I38", "I41:I49", "I52:I53", "I56:I60", "I63:I67", "I70:I72", "I75:I76", "I79:I85", "I88:I92", "I95:I106", "I109:I114", "I117:I126", "I129:I133", "I136:I147", "I150:I153", "I156:I163", "I166:I167", "I170:I174", "I177", "I180:I182", "I185", "I188:I190", "I193", "I196:I205", "I208:I214", "I217"];
var values = rangeList;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
RangeListApp.getSpreadsheet(spreadsheet).getRangeList(destinationList).setValues(values);

This script returns the cell ranges into column as written in the script. i.e. I4 = K4:K6, I5 = K4:K6, I6 = K4:K6 but does not return the displayed numerical summed value.

I have also tried a version where

var values = r;

My thinking was to take the output of displayed values and set those values into the new range list, however this only returned:

[object Object]

into each cell.

I know I have something wrong here in how to define or recall the values so any guidance here would be appreciated.

Many thanks in advance.

Solution

  • I believe your goal is as follows.

    • You want to copy the values from ['K4:K6', 'K9:K14', 'K17:K18',,,] to ["I4:I6", "I9:I14", "I17:I18",,,].

    Issue and workaround:

    In the current stage, unfortunately, my RangeListApp cannot copy the values with the ranges. So, in this case, as a current workaround, I would like to propose using a script with Sheets API. The sample script is as follows.

    Sample script:

    Before you use this script, please enable Sheets API at Advanced Google services.

    function myFunction() {
  var sheetName = "Sheet1"; // Please set your sheet name.

  // These ranges are from your script.
  var srcRangeList = ['K4:K6', 'K9:K14', 'K17:K18', 'K21:K24', 'K27', 'K30:K38', 'K41:K49', 'K52:K53', 'K56:K60', 'K63:K67', 'K70:K72', 'K75:K76', 'K79:K85', 'K88:K92', 'K95:K106', 'K109:K114', 'K117:K126', 'K129:K133', 'K136:K147', 'K150:K153', 'K156:K163', 'K166:K167', 'K170:K174', 'K177', 'K180:K182', 'K185', 'K188:K190', 'K193', 'K196:K205', 'K208:K214', 'K217'];
  var dstRangeList = ["I4:I6", "I9:I14", "I17:I18", "I21:I24", "I27", "I30:I38", "I41:I49", "I52:I53", "I56:I60", "I63:I67", "I70:I72", "I75:I76", "I79:I85", "I88:I92", "I95:I106", "I109:I114", "I117:I126", "I129:I133", "I136:I147", "I150:I153", "I156:I163", "I166:I167", "I170:I174", "I177", "I180:I182", "I185", "I188:I190", "I193", "I196:I205", "I208:I214", "I217"];

  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: srcRangeList.map(e => `'${sheetName}'!${e}`), valueRenderOption: "FORMATTED_VALUE" }).valueRanges;
  var data = values.map(({ values }, i) => ({ range: `'${sheetName}'!${dstRangeList[i]}`, values }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ssId);
}
    • When this script is run, the values of srcRangeList is copied to dstRangeList.

    Note:

    References: