英文:
Script to copy formulas when inserting row at the top - Google Sheets
问题
我有一个通话日志，其中有多个下拉菜单和公式，以使使用它的人更容易进行数据输入。问题是，当我插入一行（在标题下的第2行）时，数据验证仍然存在，但公式不会复制。我尝试过几个脚本，但似乎都不起作用。我有大约10列带有公式的列。
英文:
I have a call log that has multiple drop-downs and formulas for making the data entry easier for the people using it. The problem is that when I insert a row (in row 2 under header) the data validation remains but the formulas don't copy. I have tried several scripts but none of them seem to work. I have about 10 columns with formulas in them.
答案1
得分: 1
在阅读您的问题并研究您的示例后，我做出以下假设：
- 您希望在标题后添加一行新数据。
- 您希望保留旧行中的公式和数据验证规则。
- 您希望将旧行的公式复制到新行中。
如果我的假设是正确的，您可以使用以下示例来满足您的需求：
function addRowAtTop() {
var sheet = SpreadsheetApp.openById('{SPREADSHEET ID}').getSheetByName('PNC Calls');
sheet.insertRowAfter(1);
for (var i = 1; i <= 34; i++) {
var cellFormula = sheet.getRange(3, i).getFormula();
if (cellFormula != '') {
sheet.getRange(2, i).setFormula(cellFormula);
}
}
}
该代码将在顶部添加一行，然后检查旧的第一行的每个单元格。如果遇到公式，它将复制到新的第一行。
参考链接：getFormula()
请将此作为解决您问题的可能解决方案之一，如果有任何额外疑问或需要进一步澄清，请随时与我联系。
英文:
After reading your question and studying your example, I assume the following:
- You want to add a new row after the headers.
- You desire to keep the formulas and data validation rules in the old rows.
- You wish to copy the formulas of the old rows into the new one.
If my assumptions are correct, you can use the following example to fulfill your requests:
CODE
<!-- language: lang-js -->
function addRowAtTop() {
var sheet = SpreadsheetApp.openById(
'{SPREADSHEET ID}').getSheetByName(
'PNC Calls');
sheet.insertRowAfter(1);
for (var i = 1; i <= 34; i++) {
var cellFormula = sheet.getRange(3, i).getFormula();
if (cellFormula != '') {
sheet.getRange(2, i).setFormula(cellFormula);
}
}
}
BEHAVIOUR
The previous code will add a row at the top, and after that it will check each cell of the old first row. If it encounters a formula, it will copy it to the new first row.
ALLUSIONS
Please take this as one of the possible solutions to your issue, and don't hesitate to write me back with any additional doubts or requests to further clarifications.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论