当我们在 B 列中达到累积值 = 50时,我正在尝试使用 Google App Script 找到方法来拆分此电子表格的 A列。如果累积值不完全匹配,我们需要在我们所在的最后一行停止每个拆分累积值低于 50。我在 C 列中添加了预期的拆分数组结果。
这是示例电子表格:https ://docs.google.com/spreadsheets/d/1_8ZRTxd64qbxCHrhwDoo4ugWHy7jG1VIKv8hHjtp3Bw/edit#gid=0
最终目标是将每个结果数组的值存储在文本文件中,然后上传到 Drive 文件夹中。
在此先感谢您的帮助,
===========================================
编辑,从@Tanaike 脚本,我更新了如下脚本,但没有运气:
function test2() {
var raw_values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange("A2:J").getValues();
var values = raw_values.map((x) => [x[0], x[2], x[4]])
var destFolderID = "1Qq52QRpYYG_T2AxNWDz0rykZbAGhdpoe";
var fileName = "sample";
createTsv_new(values, destFolderID, fileName)
}
function createTsv_new(values, destFolderID, fileName) {
const folderId = destFolderID; // Please set the folder ID you want to put the created files.
const { res } = values.reduce((o, [s, a, b], i, v) => {
o.tempC += b;
o.tempAr.push([s, a]);
if (o.tempC + (v[i + 1] ? v[i + 1][4] : 0) > 50 || i == v.length - 1) {
o.res.push(o.tempAr);
o.tempAr = [];
o.tempC = 0;
}
return o;
}, { res: [], tempAr: [], tempC: 0 });
if (res.length == 0) return;
res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(fileName + (i + 1) + ".tsv", e.join("\t")));
}
目前,预期的拆分文件结果如下:更新后的预期文件结果
在您的情况下,以下示例脚本怎么样?
function myFunction1() {
const folderId = "root"; // Please set the folder ID you want to put the created files.
// 1. Retrieve values from Spreadsheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1:B" + sheet.getLastRow());
const [header, ...values] = range.getValues();
// 2. Create an array including the separated rows.
const { res } = values.reduce((o, [a, b], i, v) => {
o.tempC += b;
o.tempAr.push(a);
// Here, the rows are separated.
if (o.tempC + (v[i + 1] ? v[i + 1][1] : 0) > 50 || i == v.length - 1) {
o.res.push(o.tempAr);
o.tempAr = [];
o.tempC = 0;
}
return o;
}, { res: [], tempAr: [], tempC: 0 });
// 3. Create text files using the created array.
if (res.length == 0) return;
res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(`Array${i + 1}.txt`, [header[0], ...e].join("\n")));
}
Array1.txt
, Array2.txt
,,,.[header[0], ...e].join("\n")
为e.join("\n")
.关于您的以下第二个新问题,
你能再帮忙吗,我改变了电子表格中列的配置:docs.google.com/spreadsheets/d/…。我想在每个最终文件中包含 A 列和 C 列,并且 cumul 现在基于 E 列。最终文件将是一个 .TSV 文件。你能再帮忙吗?
示例脚本如下。
function myFunction2() {
const folderId = "root"; // Please set the folder ID you want to put the created files.
// 1. Retrieve values from Spreadsheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1:E" + sheet.getLastRow());
const [header, ...values] = range.getValues();
// 2. Create an array including the separated rows.
const { res } = values.reduce((o, [a, , c, , e], i, v) => {
o.tempC += e;
o.tempAr.push([a, c].join("\t"));
// Here, the rows are separated.
if (o.tempC + (v[i + 1] ? v[i + 1][4] : 0) > 50 || i == v.length - 1) {
o.res.push(o.tempAr);
o.tempAr = [];
o.tempC = 0;
}
return o;
}, { res: [], tempAr: [], tempC: 0 });
// 3. Create text files using the created array.
if (res.length == 0) return;
res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(`Array${i + 1}.txt`, [[header[0], header[2]].join("\t"), ...e].join("\n")));
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句