RangeListApp is a GAS library for retrieving, putting and replacing values for Spreadsheet by a range list with a1Notation using Google Apps Script (GAS).
There is Class RangeList as one of classes for Spreadsheet. There is setValue(value)
in Class RangeList as a method. setValue(value)
puts value
to the cells of range list. Recently, when I used this method, I noticed that the following situations what I want cannot be achieved.
Although these might be able to be used by the future update, I created this library, because I wanted to achieve them soon. If this library is also useful for you, I'm glad.
1L44xvi-AsdwbYgqeb63e2i-7PpC01Mt-1fgNO2HpTTvAHPMU1HteAIsY
1L44xvi-AsdwbYgqeb63e2i-7PpC01Mt-1fgNO2HpTTvAHPMU1HteAIsY
.Installing is done! You can use RangeListApp.
At the first run of the script using library, the authorization screen is displayed. Please authorize it. By this, you can use Sheets API. This authorization is required to do for only the first run.
Methods | Return | Descriptions |
---|---|---|
getValues() | Object | Retrieve values from each cell using range list. This works like [getValues() of Class Range](https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()). |
getDisplayValues() | Object | Retrieve display values from each cell using range list. This works like [getDisplayValues() of Class Range](https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValues()). |
getFormulas() | Object | Retrieve formulas from each cell using range list. This works like getFormulas() of Class Range. |
setValues(values) | Object | Put values to cells of range list. This works like setValues() of Class Range. Inputted the range list is returned. |
replaceValues(regex, value) | Object | Replace values of cells of range list using regex. Inputted the range list is returned. |
replaceFormulas(regex, value) | Object | Replace formulas of cells of range list using regex. Inputted the range list is returned. |
setCheckBox(values) | Object | Put checkbox to cells of range list. Inputted the range list is returned. |
expandA1Notations(rangeList) | Object | Expand a1Notation. For example, A1:C2 is expanded to "A1","B1","C1","A2","B2","C2" . |
I would like to add more methods in the future.
These methods retrieve values from each cell using range list.
As the samples, it uses the following sheets.
In this sample script, the values of "A1", "B2", "C3:D4", "sheet2!A3", "sheet2!B2:B5", "sheet2!D6:E7"
are retrieved using getValues().
var rangeList = ["A1", "B2", "C3:D4", "sheet2!A3", "sheet2!B2:B5", "sheet2!D6:E7"];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).getValues();
// var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).getFormulas();
// var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).getDisplayValues();
These values can be retrieved by one API call. All values are 2 dimensional array.
[
{"range":"Sheet1!A1","values":[["sheet1_A1"]]},
{"range":"Sheet1!B2","values":[["sheet1_B2"]]},
{"range":"Sheet1!C3:D4","values":[["sheet1_C3","sheet1_D3"],["sheet1_C4","sheet1_D4"]]},
{"range":"sheet2!A3","values":[["sheet2_A3"]]},
{"range":"sheet2!B2:B5","values":[["sheet2_B2"],[],[],["sheet2_B5"]]},
{"range":"sheet2!D6:E7","values":[["sheet2_D6","sheet2_E6"],["sheet2_D7"]]}
]
This method put values to each cell using range list.
var rangeList = ["A1", "B2", "C3:D4", "sheet2!A3", "sheet2!B2:B5", "sheet2!D6:E7"];
var values = ["sample1", 123, "sample2", "=sum(B2:B5)", "=ROW() * 2", true];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).setValues(values);
rangeList
and values
are required to be the same. For example, 2nd index of rangeList
("B2"
) is corresponding to the 2nd index of values
(123
).rangeList
like "C3:D4"
, "sample2"
is put to all cells of range."=sum(B2:B5)"
. This library checks the top letter of each element in values
. When it is =
, the element is put as a formula.=ROW() * 2
as a string, you can put it by giving ==ROW() * 2
.
==
is used for the top letter of formula, the formula is put as a string. When it is put, ==
is converted to =
.var values = "sample";
is used, "sample" is put to all cells of "rangeList". This works like "setValue(value)" of Class RangeList.These methods replace values of each cell using range list by regex.
var rangeList = ["A1", "B2", "C3:D4"];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var regex = new RegExp("[a-d]");
var value = "foo";
var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).replaceValues(regex, value);
regex
.regex
and value
which can be used is one value, respectively.replaceFormulas()
instead of replaceValues()
is used, it can replace formulas in each cell.This method puts checkbox to each cell using range list.
var rangeList = ["A1", "B2", "C3:D4"];
var values = [[], [], ["foo", "bar"]];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var r = RangeListApp.getSpreadsheet(spreadsheet).getRangeList(rangeList).setCheckBox(values);
setValues()
.["foo", "bar"]
of values
means true
and false
, respectively.var rangeList = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
var r = RangeListApp.expandA1Notations(rangeList);
[
["A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3"],
["B10","C10","D10","E10","F10","G10","H10","I10","J10","K10","L10","M10","N10","O10","P10","Q10","R10","S10","T10","U10","V10","W10","B11","C11","D11","E11","F11","G11","H11","I11","J11","K11","L11","M11","N11","O11","P11","Q11","R11","S11","T11","U11","V11","W11","B12","C12","D12","E12","F12","G12","H12","I12","J12","K12","L12","M12","N12","O12","P12","Q12","R12","S12","T12","U12","V12","W12","B13","C13","D13","E13","F13","G13","H13","I13","J13","K13","L13","M13","N13","O13","P13","Q13","R13","S13","T13","U13","V13","W13"],
["EZ5","FA5","EZ6","FA6","EZ7","FA7","EZ8","FA8"],
["AAA1","AAB1","AAA2","AAB2","AAA3","AAB3"]
]
If you have any questions and commissions for me, feel free to tell me.
v1.0.0 (July 27, 2018)
v1.0.1 (September 13, 2018)
A1:C2
is expanded to "A1","B1","C1","A2","B2","C2"
.