Neither 1D array, nor 2D array working for setValues() in google scripts

Question

I'm trying to add column headings to a sheet if they are needed. In this test data, there are two new column headings I'm trying to add. I've tried using both a 1D array and a 2D array, but neither is working. I'm trying to use answers like this one on SO. I must be missing something simple.

Here's what I tried first:

163.    console.log("aMissing_Contracts (1D Array): " + aMissing_Contracts);
164.    let targetContractsRange = targetSheet.getRange(1,targetLastColumn + 1, 1, 2);
165.    targetContractsRange.setValues(aMissing_Contracts)
166.    exit();

But the console showed:

5:49:22 PM  Notice  Execution started
5:49:23 PM  Info    aMissing_Contracts (1D Array): extra contract 2,extra contract 1
5:49:23 PM  Error   
Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.
controller  @ GetBalances.gs:165

So, I tried:

    // get range of current contracts
    if (aMissing_Contracts.length >0) {       // are there any missing contracts?
        /** turn aMissing_contracts into 2d array: aTwoDMissing_Contracts */
        var aTwoDMissing_Contracts = [];
        for (var d = aMissing_Contracts.length - 1; d >= 0; d--){ 
           var aTempArray = [];
           aTempArray[0] = aMissing_Contracts[d]
           aTwoDMissing_Contracts.push(aTempArray);
        };

        let targetContractsRange = targetSheet.getRange(1,targetLastColumn + 1, 1, 2);                                 
        console.log(aTwoDMissing_Contracts)
        console.log("the range: " + targetContractsRange.getA1Notation());  
        console.log("the missing contracts: "  + aTwoDMissing_Contracts);
183.    targetContractsRange.setValues(aTwoDMissing_Contracts);
    };

and I got this in the console:

6:05:57 PM  Info    [ [ 'extra contract 1' ], [ 'extra contract 2' ] ]
6:05:57 PM  Info    the range: AZ1:BA1
6:05:57 PM  Info    the missing contracts: extra contract 1,extra contract 2
6:05:57 PM  Error   
Exception: The number of rows in the data does not match the number of rows in the range. The data has 2 but the range has 1.
controller  @ GetBalances.gs:183

AZ1:BA1 is one row deep and the array only has two sub-arrays. What am I missing?

Wicket · Accepted Answer · 2022-02-01 23:27:03Z

1

The array [ [ 'extra contract 1' ], [ 'extra contract 2' ] ] has two rows and one column, so instead of

let targetContractsRange = targetSheet.getRange(1,targetLastColumn + 1, 1, 2);

use

let targetContractsRange = targetSheet.getRange(1,targetLastColumn + 1, 2, 1);

Or change the array shape to

[ [ 'extra contract 1' , 'extra contract 2' ] ]

answered Feb 1, 2022 at 23:27

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DBWeinstein Over a year ago

Thank you. I feel like an idiot.

Collectives™ on Stack Overflow

Neither 1D array, nor 2D array working for setValues() in google scripts

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related