Abstract
This report showcases a practical application of Google Apps Script, demonstrating how new JavaScript methods can be used to create a script that automatically transfers selected rows between sheets in a Google Sheet.
Introduction
JavaScript, a fundamental pillar of contemporary web development, has experienced a significant rise in popularity due to its versatility and widespread adoption. As JavaScript’s influence has expanded, so too has Google Apps Script, a cloud-based scripting language constructed on the V8 JavaScript engine. This evolution has led to the introduction of novel methods and features, thereby expanding the capabilities of developers working within the Google Workspace ecosystem.
In this report, we will delve into a practical application of Google Apps Script, showcasing the effective utilization of these newly introduced methods. Specifically, we will present a sample script designed to automatically transfer selected rows from one sheet to every other sheet within a Google Sheet. By harnessing the enhanced functionalities of modern JavaScript methods, this script demonstrates how developers can create more efficient and streamlined solutions within the Google Apps Script environment. This example provides valuable insights into the potential benefits of adopting these new methods and encourages developers to explore how they can be applied to their own projects.
Samples
Sample 1
Create a new Google Spreadsheet and put the following values of “Input” situation.
In this sample, from the “main” sheet, the 3 sheets “ID1”, “ID2”, and “ID3” are created and the values of each ID are put into each sheet. When a script is created to achieve this goal using a new method of Javascript, it becomes as follows:
function sample1() {
const sheetName = "main";
const keyColumn = 1; // Column "A"
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
Map.groupBy(
sheet
.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())
.getDisplayValues(),
(r) => r[keyColumn - 1]
).forEach((v, k) => {
const s = ss.getSheetByName(k) || ss.insertSheet(k);
s.getRange(s.getLastRow() + 1, 1, v.length, v[0].length).setValues(v);
});
}
In this script, Map.groupBy() is used. When console.log(JSON.stringify([...Map.groupBy(,,,)])) is run to this script, the following value is returned. You can see the rows are split by the values of column “A”.
[
[
"ID1",
[
["ID1", "b2", "c2", "d2", "e2"],
["ID1", "b5", "c5", "d5", "e5"],
["ID1", "b6", "c6", "d6", "e6"]
]
],
[
"ID2",
[
["ID2", "b3", "c3", "d3", "e3"],
["ID2", "b8", "c8", "d8", "e8"],
["ID2", "b9", "c9", "d9", "e9"],
["ID2", "b10", "c10", "d10", "e10"]
]
],
[
"ID3",
[
["ID3", "b4", "c4", "d4", "e4"],
["ID3", "b7", "c7", "d7", "e7"]
]
]
]
For example, when Map.groupBy(,,,) is modified using the method without “Map.groupBy()”, it becomes as follows.
From:
Map.groupBy(
sheet
.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())
.getDisplayValues(),
(r) => r[keyColumn - 1]
);
To:
sheet
.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())
.getDisplayValues()
.reduce((m, r) => {
const k = r[keyColumn - 1];
return m.set(k, m.has(k) ? [...m.get(k), r] : [r]);
}, new Map());
As additional information, when benchmarking “Map.groupBy” and “reduce and Map” it was found that “Map.groupBy” had a processing cost approximately 10 % lower than “reduce and Map”. This result suggests that “Map.groupBy” can be used to reduce both the processing cost and the complexity of the script.
Sample 2
In this sample, it supposes that “Sample 1” has already been finished. And, the Spreadsheet of “Sample 1” is used.
In this sample, after “Sample1” was finished, the new data of the “main” sheet is put into “ID1”. The duplicated rows are checked. When a script is created to achieve this goal using a new method of Javascript, it becomes as follows:
function sample2() {
const sheetName = "main";
const keyColumn = 1; // Column "A"
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
Map.groupBy(
sheet
.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())
.getDisplayValues(),
(r) => r[keyColumn - 1]
).forEach((v, k) => {
const s = ss.getSheetByName(k) || ss.insertSheet(k);
const dstValues = new Set(
s
.getDataRange()
.getDisplayValues()
.map((r) => r.join(""))
);
const srcObj = new Map(v.map((r) => [r.join(""), r]));
const check = new Set(srcObj.keys()).difference(dstValues);
if (check.size > 0) {
const values = [...check].map((e) => srcObj.get(e));
s.getRange(
s.getLastRow() + 1,
1,
values.length,
values[0].length
).setValues(values);
console.log(`${values.length} items were added to the "${k}" sheet.`);
}
});
}
In this script, the rows split by “Map.groupBy()” are compared with the existing values in each sheet using Set.prototype.difference(). By this, only the new rows are appended to each sheet.
When the script in the forEach loop is modified without using “Set.prototype.difference()”, it becomes as follows.
From:
const check = new Set(srcObj.keys()).difference(dstValues);
To:
const check = new Set();
srcObj.keys().forEach((e) => {
if (!dstValues.has(e)) {
check.add(e);
}
});
As additional information, in this case, when benchmarking “Set.prototype.difference” and “Set, forEach” it was found that “Set.prototype.difference” had a processing cost approximately 40 % higher than “Set, forEach”. This result suggests that “Set.prototype.difference” can be used to reduce the complexity of the script while the processing cost is increased. I guess that this might depend on the use case.