Google Apps 脚本 - 如何将一列复制到下一个可用列的另一工作表 [英] Google Apps Script - How to copy a column(s) to another sheet at next available column

问题描述

我有一个要求，我需要从一列(或多列)中获取值并将它们复制到下一个可用列中的另一张工作表中.我写了一个这样的脚本.它确实从一列复制值，但无法继续将同一源列中的新数据的另一个快照复制到目标工作表的下一个空闲列.

I have a requirement where I need to take values from one column (or more) and copy them to another sheet in the next available column (s). I have written a script like this. It does copy the values from one column but it has no way to move forward for taking another snapshot of new data in same source column to destination sheet's next free column.

*//keep a copy of Sales numbers for every month
function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales plan");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SalesRecordsMonthly");

// Copy from 17th row, 4th column, all rows for one column 
var rangeToCopy = sheetFrom.getRange(17, 4, sheetFrom.getMaxRows(), 1);

//Paste to another sheet from first cell onwards
rangeToCopy.copyTo(sheetTo.getRange(1, 1));
}*

我很抱歉格式不正确:(我需要修改此脚本以提及源工作表中的任何列集并将它们复制到目标工作表.对于新的月份，它应该在下一组列中执行相同的操作，而不是像现在那样覆盖.此外，复制应该只针对尚未丢失的值发生.我知道脚本中有一个 ContentOnly 选项，但不确定如何使用它.

I am sorry about the poor formatting :( I need to modify this script to mention any set of columns from source sheet and copy them to destination sheet. And for new month, it should do the same in next set of columns, instead of overwriting as it does now. Also, the copy should happen only for values which is missing yet. I know there's an option of ContentOnly in script but not sure how to use it.

推荐答案

如果我理解正确，这里有一个代码可以满足您的要求，即从第 17 行的第 4 列中的一个工作表中获取值并将其复制到另一个工作表不会覆盖从第 1 行开始的列

If I understood correctly, here is a code that does what you wanted, ie get the values from one sheet in column4 from row 17 and copy it to the other sheet without overwriting to columns starting at row 1

function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales plan");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SalesRecordsMonthly");

// Copy from 17th row, 4th column, all rows for one column 
var valuesToCopy = sheetFrom.getRange(17, 4, sheetFrom.getLastRow(), 1).getValues();

//Paste to another sheet from first cell onwards
sheetTo.getRange(1,sheetTo.getLastColumn()+1,valuesToCopy.length,1).setValues(valuesToCopy);
}

测试表此处，复制以运行脚本 - 仅查看

test sheet here, make a copy to run the script - view only

这仅涵盖您问题的第一部分，第二部分有点令人困惑(如我在上面的评论中所述).

This covers only the first part of your question, the second part was a bit confusing (as mentioned in my comment above).

这篇关于Google Apps 脚本 - 如何将一列复制到下一个可用列的另一工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！