Google表格复制公式并插入行 [英] Google Sheets Copy down formula with inserting a row

问题描述

我正在从Excel和VBA过渡到Google表格，并且，不懂JavaScript.

我看到有一些对VBA的共同要求，当我在数据集中的任何地方插入新行时，这些请求都将从上一行的公式中复制下来.我知道如何添加对接，并且我使用了宏记录器来记录行的插入，但是记录器仅记录了我选择在上面插入的特定行.

我希望脚本捕获活动单元格的行号，以便它在我的光标所在的位置都是动态的.

这是我到目前为止的代码.有人可以帮我改正吗?

  function test(){var电子表格= SpreadsheetApp.getActive();电子表格.getActiveCell().getRow().activate();电子表格.getActiveSheet().insertRowsBefore(spreadsheet.getActiveCell().getRow()，1);电子表格.getActiveCell().offset(0，0，1，电子表格.getActiveCell().getNumColumns()).activate();}; 

解决方案

解决方案:

您可以通过创建

3. 点击页面右下角的添加触发器.

4. 然后完全使用 这些设置:

I am transitioning from Excel and VBA to Google Sheets and admittedly, don't know JavaScript.

I see that there is a common request for some VBA which will copy down the formulas from the row above, when I insert a new row anywhere in the dataset. I know how to add a butt, and i used the Macro Recorder to record the insertion of a Row, but the recorder only records that particular row that I chose to insert above.

I'd like the script to grab the activecell row number, so that it is dynamic on where ever my cursor is.

Here's the code I have so far. Can someone help me correct it?

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveCell().getRow().activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveCell().getRow(), 1);
  spreadsheet.getActiveCell().offset(0, 0, 1, spreadsheet.getActiveCell().getNumColumns()).activate();
};

解决方案

Solution:

You can accomplish your goal by creating an installable onChange trigger.

Follow the instructions below to set it up. After the setup is completed, you don't execute myFunction manually. It will be executed automatically everytime you insert a new row in the active sheet.

function myFunction(e) {
 const sh = SpreadsheetApp.getActiveSheet();
 if(e.changeType === 'INSERT_ROW') {
 const row = sh.getActiveRange().getRow();
 const formulas = sh.getRange(row-1,1,1,sh.getLastColumn()).getFormulasR1C1();
 sh.getRange(row,1,1,sh.getLastColumn()).setFormulasR1C1(formulas);  
  }
}

---

Instructions:

1. Go to use the aforementioned code, copy/paste it to the script editor and click save.

2. Click on the current project's triggers:

3. Click on add trigger on the bottom right side of the page.

4. And then use exactly these settings:

这篇关于Google表格复制公式并插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！