Cant update an array of values to a single range in Google sheet

Question

I have successfully used "spreadsheets#get" request of Google Sheets API for a specified range, and done some calculation on each of the items in the array, storing them to a new array.

Now I need to update another range in the Google Sheet with the array items I have calculated.

I have successfully fetched data from a single cell, done calculations on that value, and written the new data to another cell. But when I am working on a range of cells, I get the error

"Requested writing within range [BILLING!D2:D], but tried writing to column [E]"

where [BILLING! D2:D] is the range I wanted to update.

Here's some of my code

Reading Data

  const sheets = google.sheets({version: 'v4', auth});
  let ranges = ['BILLING!A2:A']
  sheets.spreadsheets.values.get({
      spreadsheetId: '1F1ed24fgZIQChueq1AGrKIYRXKrX-vK3RAIYKd5lVrE',
      range: ranges,
  }, (err, res) => {
      let sma_array = [];
      async(res.data.values, (value, callback) => {
          let id = value[0];
          let url = 'https://www.quandl.com/api/v3/datasets/BSE/BOM' + id + '?limit=57&api_key=' + MY_API_KEY;
          reqs.requestFun(url, id).then((result, err) => {
              sma_array.push(result)
              console.log(id,result)
              setTimeout(function () { callback(); }, 500);
          })            
      }, function (err) {
          writing(sma_array)
      });
  })

Input array from A2:A:-

[ [ '532424' ],
  [ '500003' ],
  [ '524634' ],
  [ '531978' ],
  [ '500493' ],
  [ '532321' ],
  [ '530307' ],
  [ '532622' ],
  [ '500300' ],
  [ '530001' ],
  [ '532457' ],
  [ '524494' ],
  [ '524731' ],
  [ '500233' ],
  [ '532714' ],
  [ '524500' ],
  [ '539229' ],
  [ '524084' ],
  [ '538019' ],
  [ '533179' ],
  [ '523539' ],
  [ '532805' ],
  [ '500367' ],
  [ '531431' ],
  [ '503811' ],
  [ '532343' ],
  [ '504212' ],
  [ '507880' ],
  [ '532953' ],
  [ '540743' ] ]

Writing Data

let writing = function (sma_array) {
    console.log(sma_array)
    let values = [
        sma_array
    ]
    const resource = {
        values: values
    }
    let ranges = ['BILLING!D2:D']
    sheets.spreadsheets.values.update({
        spreadsheetId: '1F1ed24fgZIQChueq1AGrKIYRXKrX-vK3RAIYKd5lVrE',
        range: ranges,
        valueInputOption: 'RAW',
        resource: resource,
    }, (err, result) => {
        if (err) {
            // Handle error
            console.log(err);
        } else {
            console.log('%d cells updated.', result.updatedCells);
        }
    });
}

The array that needs to get an update to D2:D : -

[ [ 702.8052631578946,
       198.67719298245612,
       105.9236842105263,
       1137.5780701754388,
       496.70263157894743,
       329.87456140350884,
       78.04912280701757,
       115.24473684210525,
       784.7464912280702,
       490.9464912280701,
       56.51228070175439,
       841.3201754385965,
       498.77192982456154,
       554.2035087719298,
       268.8666666666667,
       199.00438596491227,
       37.27543859649123,
       2592.591228070176,
       27.73684210526315,
       626.8912280701755,
       205.12368421052628,
       85.49035087719298,
       40.75175438596491,
       391.38947368421043,
       369.10789473684207,
       488.36315789473656,
       227.23596491228068,
       458.780701754386,
       206.2780701754385,
       208.4875856548958 ] ]

Error

 errors:
   [ { message:
        'Requested writing within range [BILLING!D2:D3], but tried writing to column [E]',
       domain: 'global',
       reason: 'badRequest' } ] }

Can you add any details like: code used, error problem encountered? [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask), [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) Show the community what you have tried. — Jessica Rodriguez, Apr 08 '19 at 14:47
Probably your `ValueRange` array does not match the dimensions you requested. But since you don't include any code here, we can't know for sure. — tehhowch, Apr 09 '19 at 12:15
I have updated my code of reading data from range A2:A and writing to D2:D — Ameerudheen.K, Apr 09 '19 at 16:27
@tehhowch updated the question with both input data and output data — Ameerudheen.K, Apr 10 '19 at 15:10
Do you see the difference in the array from reading and the array you're attempting to write, dimension-wise? — tehhowch, Apr 10 '19 at 15:13
Possible duplicate of [Update value in Google Sheet APIv4 with Python](https://stackoverflow.com/a/45013297/9337071) — tehhowch, Apr 10 '19 at 15:14
Welcome. _let ranges = ['BILLING!D2:D']_ the problem here is that the number of rows could be any value at all, and that you need a specific number to define the range when you `setValues`. Use `getLastRow` or [this utility](https://stackoverflow.com/a/17637159/1330560) (highly recommended). to specifically establish the last row, then use that value when defining a range. — Tedinoz, Apr 11 '19 at 07:53
Thanks, @tehhowch ... Issue solved when I made my output array as output = [[1],[2],[3],[4]].. instead of [1,2,3,4] Changing major dimension also may work but I got some syntax errors — Ameerudheen.K, Apr 16 '19 at 11:39

score 0 · Accepted Answer · answered Apr 17 '19 at 05:48

The Problem solved by changing majorDimension of output to column or by changing output array as like major dimension=ROW. Before my array was like array = [1,2,3,4] and now changed to array = [[1],[2],[3],[3]].

For output, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS will return [[1,3],[2,4]].

For input, with range=A1:B2,majorDimension=ROWS then [[1,2],[3,4]] will set A1=1,B1=2,A2=3,B2=4. With range=A1:B2,majorDimension=COLUMNS then [[1,2],[3,4]] will set A1=1,B1=3,A2=2,B2=4.

Cant update an array of values to a single range in Google sheet

1 Answers1