CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Ray
Ray

Reputation: 189

Cannot Set An Array From Google Web App To Google Sheet

I'm trying to combine a new value to an existing value in Google Sheet. So when user input the codeInput then it will find the codeInput row and add formObject.things value that user just filled to the existing thingsList value (which the same row as codeInput). I tried this code but it return blank to the Google Sheet.

Your response would be very appreciated.

Here's my code:

function update1(formObject) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName("Sheet1");
    var data = ws.getRange(5, 1, ws.getLastRow()-1, 1).getValues();
      
    var codeList = data.map(function(r){return r[0].toString(); });
    var thingsList = data.map(function(r){return r[8]; });
    var position = codeList.indexOf(formObject.codeInput);
    var array = [thingsList[position], formObject.things]

    if (position > -1) {
        position += 5;
        ws.getRange(position,9).setValue(array);
    }
}

Upvotes: 0

Views: 42

Answers (1)

Wicket
Wicket

Reputation: 38131

Regarding

var array = [thingsList[position], formObject.things]

and

 ws.getRange(position,9).setValue(array);
  1. The type of array is Array
  2. setValue doesn't allow Array objects as argument. It accepts, string, number, Date and Boolean

Instead of setValue

  1. you could pass a Google Sheets array (i.e. ={1,2}) by using setFormula
  2. you could pass a bidimenstional Array to Google Sheets by using setValues
  3. you could pass an array (unidimensional Array) by using appendRow

Regarding

var data = ws.getRange(5, 1, ws.getLastRow()-1, 1).getValues();

and

var thingsList = data.map(function(r){return r[8]; });

data is and Array possible having multiple rows but it has only one column, so, r[8] returns null which cause that thinkslist be an array of null values.

So far the question doesn't include enough details to know what could be the fix needed, but here are two possibilities among others :

var data = ws.getRange(5, 1, ws.getLastRow()-1, 9).getValues();

or

var data = ws.getRange(5, 1, ws.getLastRow()-1, ws.getLastColumn()).getValues();

Upvotes: 3

Related Questions