Create Google Sheet from JSON object using Google Apps Script

Question

JSON example:

[
   [
      SERV1,
      {
         "QOS Script Version=Not Installed",
         Host_Name=ABC,
         IP Address=123.123.123.123,
         Free Space=244Gi,
         Time Ran=Sat Nov 31 07:26:02 +08 2020,
         Backup Location= VOL03,
         Date of Last Backup=2020-26-28-031839,
         Last Reboot Time=          Nov 18 02:00:13 2020,
         Total Storage=1.8Ti,
         OS Version=10.15.6
      }
   ],
   [
      DATASTORE,
      {
         "QOS Script Version=Not Installed",
         Date of Last Backup=2020-11-28-031520,
         Free Space=34Gi,
         IP Address=123.123.123.12,
         OS Version=10.15.6,
         Total Storage=1.8Ti,
         Backup Location= VOL03,
         Time Ran=Sat Nov 15 07:23:48 +08 2020,
         Host_Name=ABC,
         Last Reboot Time=          Nov 28 02:04:21 2020
      }
   ]
]

I need this to be dumped into a google sheet in this sort of format:

+-----------+-----------------+------------+-----------------+------+
| Server    | Backup Location | Free Space | IP Address      | etc. |
+-----------+-----------------+------------+-----------------+------+
| SERV1     | VOL03           | 244Gi      | 123.123.123.123 |      |
+-----------+-----------------+------------+-----------------+------+
| DATASTORE | VOL01           | 1.8Ti      | 123.123.123.124 |      |
+-----------+-----------------+------------+-----------------+------+
| etc.      |                 |            |                 |      |
+-----------+-----------------+------------+-----------------+------+

Where the order of the column headers can be adjusted and the list is ordered by the server's alphabetically. I have tried to use the code in this answer but I get an error "Cannot read property 'reduce' of undefined" : Parse Nested Json Data to Google Sheets with App Script

The json is generated using this Firebase library: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase/tutorials/read-and-write-data-in-firebase-from-apps-script

var object = Object.entries(FirebaseApp.getDatabaseByUrl(url, optSecret).getData());

The data has extra whitespace and quotes around the "QOS Script Version" key which can remain in the google sheet but ideally would be removed. This will be removed from the original firebase source eventually but I am currently in need of the google sheet dump but am unable to figure out how to format it in this way. Thank you for any help you can provide.

@Cooper Thank you for the help! I was able to get it working now — Austin
– Austin, Commented Dec 13, 2020 at 21:48

Austin · Accepted Answer · 2020-12-13 21:50:45Z

0

I have managed to write the values one cell at a time. Any suggestions for code improvement are welcomed.

  // write column headers
  var object = Object.entries(data);
  var headers = [['Host_Name', 'Backup Location', 'Free Space', 'Total Storage', 'IP Address', 'Last Reboot Time', 'OS Version', 'QOS Script Version']];
  // getRange(start row, start column, number of rows, number of columns)
  sheet.getRange(1, 1, 1, headers[0].length).setValues(headers);
  var current_row = 2;
  for (const [server_key, serverData_value] of Object.entries(data)) {
    // write serverData (values)
    server_data = Object.entries(serverData_value)
    for (var i = 0; i < headers[0].length; i++){
      sheet.getRange(current_row, i+1, 1, 1).setValue(serverData_value[headers[0][i]]);
    }
    current_row++;
  }

answered Dec 13, 2020 at 21:50

Austin

1

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

Collectives™ on Stack Overflow

Create Google Sheet from JSON object using Google Apps Script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related