如何使用新的Google电子表格数据填充特定工作表中的单元格 [英] How to fill a cell in a specific sheet with data of new Google spreadsheets
问题描述
我有一个名为指标"的工作表,每天有一行,例如:
I have a sheet called "metrics" with one row for each day, for example:
day counts1 counts2 counts3
01-01-2019
01-02-2019
01-03-2019
01-04-2019
01-05-2019
...
每天我创建一个新的工作表,称为与日期相同.例如,在01-06-2019中,我创建了一个名为"01-06-2019"的工作表,并将数据输入到该工作表中.利用这些每日数据,我可以计算每天的计数1,计数2和计数3.
Each day I create a new sheet called the same as the date. For example, in 01-06-2019 I create a sheet called "01-06-2019" and enter data into it. With this daily data, I calculate for each day counts1, counts2 and counts3.
我想用那些值添加新行和相应的counts,counts2,counts3填充工作表"metrics".
I want to fill the sheet "metrics" with those values adding a new row and the corresponding counts1, counts2, counts3.
或者,这是保存数据并实现此目标的另一种更好的方法吗?
Or, is it another better way to save the data and achieve this goal?
推荐答案
粘贴到B2单元格中并向下拖动蓝色小方块
={SUM( INDIRECT(TO_TEXT(A2)&"!B2:C2")),
AVERAGE(INDIRECT(TO_TEXT(A2)&"!C2:C")),
SUM( INDIRECT(TO_TEXT(A2)&"!B2:B"))}
function onOpen() {
SpreadsheetApp.getUi().createMenu('NEW DAY')
.addItem('Create New Tabs', 'createTabs')
.addToUi()
}
function createTabs() {
var ss = SpreadsheetApp.getActive()
ss.getSheetByName('metrics').getRange('A2:A').getValues().filter(String)
.forEach(function (sn) {
if (!ss.getSheetByName(sn[0])) {
ss.insertSheet(sn[0], ss.getSheets().length);
}
})
}
- 添加此脚本
- 使用 F5 重新加载电子表格
- 选择A列
- 单击 123 按钮
- 并选择
Plain text
- 在A列中添加新的日子
- 然后单击 NEW DAY
- 选择创建新标签页
- add this script
- reload spreadsheet with F5
- select column A
- click on 123 button
- and select
Plain text
- add new days in column A
- then click on NEW DAY
- select Create New Tabs
这将通过跳过已创建的标签页/工作表并跳过空单元格从A列中的日期列表中自动创建新标签页
this will automatically create new tabs from a list of dates in A column by skipping already created tabs/sheets and also skipping empty cells
为避免每次都输入日期,请在 A2 单元格中使用此公式并向下拖动:
to avoid typing in dates each time, use this formula in A2 cell and drag down:
=TO_TEXT(TEXT(DATE(2019, 1, 1)+ROW()-2, "MM-dd-yyyy"))
如何向电子表格中添加脚本
- 转到工具
-
选择脚本编辑器
how to add a script to your spreadsheet
- go to Tools
select Script editor
复制粘贴脚本
以某个名称保存项目
单击运行图标并对其进行授权...
click on run icon and authorise it...
选择您的帐户
点击高级
选择转到*(不安全)
单击允许并返回到工作表(您可以关闭脚本窗口/选项卡)
click on Allow and return to your sheet (you can close script window/tab)
这篇关于如何使用新的Google电子表格数据填充特定工作表中的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!