google-apps-script - 创建一个循环遍历所有工作表的宏
问题描述
以下宏代码创建一个简单的散点图。我的 Google 电子表格中有 100 张表格，我只想让代码循环遍历每张表格。
我知道我需要一个 for (var i=0, i<101 (sheets.lentgh, i++1)....
我只是想弄清楚在哪里放置指示代码循环遍历每张工作表的 I 。
function _1vs28() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('G1').activate();
var sheet = spreadsheet.getActiveSheet();
var chart = sheet.newChart()
.asLineChart()
.addRange(spreadsheet.getRange('F1:I97'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
var charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('F1:I97'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('G1:G100000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('I1:I10000'))
.addRange(spreadsheet.getRange('G1:G10000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', true)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('I1:I10000'))
.addRange(spreadsheet.getRange('G1:G10000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', true)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
spreadsheet.getRange('H978').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
};
function newsheet() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B17').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Glucocil'), true);
};
function newsheet1() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B20').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Glucocil'), true);
spreadsheet.getRange('C16').activate();
};```
解决方案
循环浏览所有工作表
function loopThroughAllSheets() {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
//var html='<h1>All My Sheets</h1>';
for(var i=0;i<shts.length;i++) {
//html+=Utilities.formatString('Sheet Name: <strong>%s</strong><br />',shts[i].getName());
doItOverAndOver(shts[i],ss);
}
//html+='<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
//var userInterface=HtmlService.createHtmlOutput(html);
//SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Sheets');
}
要回答您的其他问题，您对此做了什么？好吧，如果您对当前脚本感到满意，那么也许您可以将其转换为如下所示的函数并如上所示调用它；
function doitOverAndOver(sht,ss) {
var spreadsheet = ss;
spreadsheet.getRange('G1').activate();//I think you need a sheet specifier in here
var sheet = sht;
var chart = sheet.newChart()
.asLineChart()
.addRange(spreadsheet.getRange('F1:I97'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
var charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('F1:I97'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('G1:G100000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', false)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('I1:I10000'))
.addRange(spreadsheet.getRange('G1:G10000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', true)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
charts = sheet.getCharts();
chart = charts[charts.length - 1];
sheet.removeChart(chart);
chart = sheet.newChart()
.asScatterChart()
.addRange(spreadsheet.getRange('I1:I10000'))
.addRange(spreadsheet.getRange('G1:G10000'))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
.setTransposeRowsAndColumns(false)
.setNumHeaders(1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('useFirstColumnAsDomain', true)
.setOption('isStacked', 'false')
.setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
.setPosition(7, 2, 350, 18)
.build();
sheet.insertChart(chart);
spreadsheet.getRange('H978').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
};
就个人而言，我想清理你的功能，但这是另一个问题。
推荐阅读
- gradle - Gradle 任务将远程项目目录下载为 zip 文件并解压缩
- cron - 如何在 Prestashop 中进行 cron 工作？
- firebase - 获取 UID 和文档数据 Firebase
- php - 在haveRaw中转义> char到>
- python - Tkinter 动态条目显示
- php - 如何修复我的 cpanel 别名和域 IP 地址在 cpanel 中不起作用
- reactjs - 制作带有自定义反应钩子的可折叠侧边栏图标
- python - obspy python中流图中的轴限制
- java - CodeIgniter API 在 android(java) 上运行两次
- python - 如何防止 Scikit-Learn Imputer 删除 NaN 行？