javascript 用于复制列值并在电子表格中插入新列的 Google 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14854109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Google Script to copy the values of a column and insert a new column in spreadsheet
提问by helpnickpls
I have started creating a spreadsheet to monitor some results from facebook, youtube etc... A lot of things I did I was able to learn from previous answers on this site.
我已经开始创建一个电子表格来监控来自 facebook、youtube 等的一些结果......我所做的很多事情我都能从这个网站上以前的答案中学到。
Now I am stuck with trying to find a script which can help me do the following:
现在我一直在努力寻找一个可以帮助我执行以下操作的脚本：
Column A contains the description of the metric
Column B contains the formula which pulls the metric from extrenal sources (e.g. number of likes of my facbeook page, number of video views of my YT channel)
A 列包含指标的描述
B 列包含从外部来源提取指标的公式（例如我的 facbeook 页面的点赞数、我的 YT 频道的视频观看次数）
I would like to have a script which runs every Monday and performs the following actions:
我想要一个脚本，它每周一运行并执行以下操作：
- Insert a new column between column B and column C (moving all columns after B down by 1 place)
- Copy column B and paste only the values (not the formulas) in the new empty column C
- Write something like Week Commencing YYYY/MM/DD on top of Column C (C1) where YYYY/MM/DD is the date of the previous week's monday
- 在 B 列和 C 列之间插入一个新列（将 B 之后的所有列向下移动 1 位）
- 复制列 B 并仅粘贴新的空列 C 中的值（而不是公式）
- 在 C 列 (C1) 的顶部写下类似 Week Commencing YYYY/MM/DD 的内容，其中 YYYY/MM/DD 是前一周的星期一的日期
I have tried a modified version of this (from http://www.kraukoblog.com/marketing/tutorial-watch-your-competitors-facebook-pages-with-google-docs/):
我已经尝试了这个的修改版本（来自http://www.kraukoblog.com/marketing/tutorial-watch-your-competitors-facebook-pages-with-google-docs/）：
function UpdateFblikes() {
var sheet =SpreadsheetApp.getActiveSheet(); //define spreadsheet name
var column = sheet.getRange("O1").getValue();
var row = 4;
Utilities.sleep(4000); // Slowdown the script (4 second)
var nblike = sheet.getRange("E4:E60"); // Select and copy the column
nblike.copyValuesToRange(sheet, column+5, column+5, row, row+56); // Paste the data
sheet.getRange(3, column+5).setValue(new Date()); // add date in column title
sheet.getRange("O1").setValue(column+1); // Incremente variable
}
But it only writes the date in the column.
但它只在列中写入日期。
This would enable me to compare the weekly changes in those metrics.
这将使我能够比较这些指标的每周变化。
Is this possible at all?
这可能吗？
采纳答案by helpnickpls
I have been able to find the solution by navigating through different tutorials on google's developers page. This is the function I have created
我已经能够通过浏览 google 开发人员页面上的不同教程找到解决方案。这是我创建的函数
function insertcolumn() {
// The code below will insert 1 column after column 5 in sheet 4
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[4];
sheet.insertColumns(5, 1);
// Get the range of cells that store data to be copied having created a named range for the column in the spreadsheet.
var valuestocopy = ss.getRangeByName("valuestocopy")
// The code below copies the range valuestocopy cells to column five up to 100 rows
valuestocopy.copyValuesToRange(sheet, 5, 5, 1, 100); }
