plot data to their respective rows

Question

There is a collection of data which needs to be shown in the spreadsheet. I mean data will have values for each row(in this case Row 1, Row 2 etc). I need to show them in the spreadsheet. How could i show those data to their respective row name. I could only find the range up to where data will be plotted.

Here is how i have done

function plotData() {
 var data = [
  {
    "Row1": "Row1 Data",
    "Row7": 5,
    "WillNotBeInOther": 0.75
  },
  {
    "Row2": 5000,
    "Row3": 0,
    "Row4": 5000,
    "Row5": null,
    "Row6": 5,
    "Row7": null,
    "Row8": 0.25,
    "Row9": 0,
    "Row10": 0
  },
  {
    "Row2": 6000,
    "Row3": 20000,
    "Row4": 26000,
    "Row5": null,
    "Row6": null,
    "Row7": null,
    "Row8": null,
    "Row9": 0,
    "Row10": 0
  },
  {
    "Row2": 7000,
    "Row3": 1000,
    "Row4": 8000,
    "Row5": null,
    "Row6": null,
    "Row7": null,
    "Row8": null,
    "Row9": 0,
    "Row10": 0
  }
]

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var lastRow = sheet.getLastRow();
  var plottableSheetRange = sheet.getRange(startRow, 3, lastRow-startRow+1, data.length);
  plottableSheetRange.setValue(10)
}

Here is the spreadsheet where I am working on

https://docs.google.com/spreadsheets/d/1tLNZv4F4lpBAnmHN5H0pBiirW4MVIfTexll9jPA03hI/edit#gid=0

code.gs is the script.

It should look like this

https://docs.google.com/spreadsheets/d/1tLNZv4F4lpBAnmHN5H0pBiirW4MVIfTexll9jPA03hI/edit#gid=1869973739

Answer 1

• You want to achieve from the following object to the image using Google Apps Script.

  • From:

    var data = [
      {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
      {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
      {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
      {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
    ];
    

  • To:

    

• You want to put the values from the cell "B4".

• As your specification of above value, you want to use only the value of Row1 from {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75} at the first index.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Before you use the following scripts, please enable Sheets API at Advanced Google services. When the all column length of the array is not the same, the method of Spreadsheets.Values.update is easy to put the array to Spreadsheet. So I used Sheets API.

Pattern 1:

In this pattern, the key names from Row1 to Row 10 are also put.

Sample script:

function plotData() {
  var data = [
    {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
    {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
    {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
    {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
  ];

  // Convert "data" to an array for putting to Spreadsheet.
  var values = data.reduce(function(ar, e, i) {
    if (i === 0) e = {"Row1": e["Row1"]}; // From your replying, the first index is modified for your specification.
    Object.keys(e).forEach(function(f) {
      var n = Number(f.replace("Row", "")) - 1;
      if (!Array.isArray(ar[n])) ar[n] = ["Row " + (n + 1)];
      ar[n][i + 1] = e[f];
    });
    return ar;
  }, []);

  // Put the converted array to Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var range = "'" + sheetname + "'!B" + startRow;
  Sheets.Spreadsheets.Values.update({values: values}, activeSheet.getId(), range, {valueInputOption: "USER_ENTERED"});
}

Pattern 2:

In this pattern, the key names from Row1 to Row 10 are not put.

Sample script:

function plotData() {
  var data = [
    {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
    {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
    {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
    {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
  ];

  // Convert "data" to an array for putting to Spreadsheet.
  var values = data.reduce(function(ar, e, i) {
    if (i === 0) e = {"Row1": e["Row1"]}; // From your replying, the first index is modified for your specification.
    Object.keys(e).forEach(function(f) {
      var n = Number(f.replace("Row", "")) - 1;
      if (!Array.isArray(ar[n])) ar[n] = [];
      ar[n][i] = e[f];
    });
    return ar;
  }, []);

  // Put the converted array to Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var range = "'" + sheetname + "'!C" + startRow;
  Sheets.Spreadsheets.Values.update({values: values}, activeSheet.getId(), range, {valueInputOption: "USER_ENTERED"});
}

Reference:

• Method: spreadsheets.values.update