How to write json data to a google sheet using javascript

j86h

I'm trying to render json data to a google sheet using javascript.

I have tried searching through loads of documentation and I've been trying for hours. I do get one line of data come into my sheet but I need to render the whole json dataset in the correct rows and columns

function renderJSON() {
  /* This function collects JSON data from a specified endpoint
  and renders the data to a spreadsheet */

  // Fetch JSON data from the endpoint
  var url = 'http://dummy.restapiexample.com/api/v1/employees';
  var response = UrlFetchApp.fetch(url);     // Get the JSON data
  var object = JSON.parse(response.getContentText());
  //Logger.log(response);

  // Define an array of all object keys
  var rows = []; // Declare an empty array
  rows.push(object);
  var headerRow = Object.keys(rows);

  // Define an array of all object values
  var rows = headerRow.map(function(key){return rows [key]});

  // Define the contents of the range
  var contents = [
    headerRow,
    rows
    ];

  // Select the range and set its values
  var ss = SpreadsheetApp.getActive();
  var rng = ss.getActiveSheet().getRange(1, 1, contents.length, 
  headerRow.length)
  var i;
  for (i = contents[0]; i < contents.length; i++) {
    rng.setValues([i]);
  }
}

I expect the whole of the json data to be in perfect format for a google sheet with the correct heading and everything aligned in the correct columns. Currently there is no output to the sheet.

Tanaike

How about this modification? In this modification, at first, the header values are retrieved from the 1st element. And the values are retrieved from the headers. Then, the values which added the headers are put to the active sheet.

Modified script:

function renderJSON() {
  var url = 'http://dummy.restapiexample.com/api/v1/employees';
  var response = UrlFetchApp.fetch(url);
  var object = JSON.parse(response.getContentText());

  var headers = Object.keys(object[0]);
  var values = object.map(function(e) {return headers.map(function(f) {return e[f]})});
  values.unshift(headers);
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

If I misunderstood your question and this was not the result you want, I apologize.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to write data to Google Sheet using PHP

How to write data to a JSON file using Javascript

How to print JSON data to a Google Sheet using GSpread

Get data from Google sheet using json

How to Import JSON data to google sheet with for loop

Google Sheet data to json

403 - Insufficient Permission to write data to Google spread sheet using Java

How can I rearrange a Google Sheet using the data within the sheet?

How can I write the data in Firestore to Google Sheet with app script?

How to Copy a data range from one sheet to another sheet using Google Sheet Script

How to make your Google sheet to read imported JSON data as a number?

How to Copy data from One Google Sheet to another Sheet in the Same file Using Sheet Name as Criteria and find the sheet base on filter criteria

Write Database data into Google sheet. Java, Googe Sheet API

How to consolidate data in google sheet using query function

How to export JavaScript object to Google Sheet using NodeJs

Write to Sheet using API and JavaScript - CORS block

How to write data into a JSON file using jquery

How to write reducers to manipulate this JSON using JavaScript

How to Append Sheet 1 to output in Sheet 2 in Google Sheets from JSON imported data

How to Access Google Photos API with Javascript using google-api-javascript-client and read JSON data?

How to Access and Insert Data in to Google Spread Sheet using C# and Sheet Api?

Split data in google sheet to another sheet using google app script

how to write data on a specific sheet of a spreadsheet

How to write Google Analytics API data to a new row, rather than a new sheet

how to write an array into an XLSX sheet using openpyxl

Read google sheet json data into chartjs

Scraped JSON Data not Pushing to Google Sheet

How to import data from another Google Sheet using Google Script without using Spreadsheet ID

How to sync the data google sheet data dynamically?

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive