Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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' ] ]

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading