0

I feel like my script is close to the goal, yet so far.

I'd like to take the current row that the user is clicked on, and then insert multiple new rows below the current row, but also continue the formulas down that are in the current row.

The script I have so far does almost all that, and the formulas continue down, but once everything is copied to the new rows, nearly each cell has a #ERROR! even though the formulas in them look written exactly the same as the source row (except for updated row numbers etc within the new formulas).

Including empty cells without any formulas.

The only formulas that seem to copy fine are very simple and locked ones such as =$A$89 but locked ones aren't the issue I need the unlocked ones to carry down.

Here's what I've got:

function newRow() {

    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    var r = sheet.getActiveRange()
        .getRow();
    var formulas = sheet.getRange(r, 1, 1, sheet.getLastColumn())
        .getFormulasR1C1();
    var nrOfCopies = formulas[0][7];
    var nrowsI = Browser.inputBox('How many rows do you want to add?', Browser.Buttons.OK_CANCEL);
    if (nrowsI == "cancel" ) {
    }
    else {
//    range[0].pop();
    for (var i = 0; i < nrowsI; i++) {
        ss.insertRowAfter(r);
        var range = sheet.getRange(r, 1, 1, sheet.getLastColumn())
        sheet.getRange(r + 1, 1, 1, formulas[0].length)
            .setFormulasR1C1(formulas);

    }}
}

As I'm newer to this, this is pieced together from other posts.

As per this post, it looks like that's an issue with using R1C1: Script to insert new row and copy down formulas that auto increase according to row index?

Is there another way to do this without the #ERROR!'s but still the formulas continuing?

  • As the first answer in the linked post mentioned, did you try using just copyTo? – Ron Kloberdanz Aug 23 '18 at 16:10
  • ok copyTo works now for some reason! not sure why it wasn't working before. thanks! – user3711262 Aug 23 '18 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.