Products

Google Sheets Importer

The JSS Google Sheets Importer extension streamlines the process of importing data, styles, validations, and formatting from Google Sheets, allowing for seamless conversion into a JSON object. This tool is ideal for constructing JSS data grids directly from Google Sheets links or setting up front-end automation.

Google Sheets to JSON
You can convert any public or private Google Sheets directly from its link to a local JSON with the appropriate permissions.

Documentation

This extension allows effortlessly importing public or private Google Sheets using the Sheet's URL or Sheet ID. Note that importing private sheets requires user authorization and necessitates proper Google Sheets API configuration, which can be done via the Google Console.

Methods

Methods that can be used with the advanced print extension

Method	Description
sheets(object?)	Set the configuration for your google sheets extension. `sheets(config?: Object) => void`
sheets(string, object?)	Import a new Google Sheets spreadsheet into the JSS json format. `sheets(ident: String, config?: Object) => void`

Configuration

Available config properties for the initial extension set up.

Method	Description
onload: Function	Method to be call after the import is finalized. You can use that to send the config to create a new Jspreadsheet Data Grid instance. `onload: (title: string, config: object) => void`

Importing private sheets

Those configurations are only required if you would like your users to import private Google Sheets spreadsheets.

Method	Description
clientId: string	yourclientapihashidentification.apps.googleusercontent.com
apiKey: string	Google API Key
clientSecret: string	Google API Secret Client Key
redirectUri: string	Google Sheets success URL return.
errorUri: string	Google Sheets import error URL return.

Security notice

The Google Sheets API credentials can be restricted by domain, meaning they can only be used from a specific domain or set of domains. This provides some level of security. However, it is still advisable to take precautions to protect these credentials, even if they are domain-restricted.

Please note that the following are just some possible additional measures to protect your keys, and it's your responsibility to make sure you implement a safe application:

Minimize exposure: While domain restrictions help limit the usage of credentials to specific domains, it's still good practice to minimize the exposure of sensitive information as much as possible. Avoid storing them directly in your JavaScript source code or any publicly accessible files.
Secure server-side storage: Store the credentials securely on your server-side code or in environment variables. Ensure that access to these credentials is restricted and follow best practices for secure storage.
Use HTTPS: Make sure your website or application is served over HTTPS. This ensures that communication between the client and server is encrypted, reducing the risk of interception and unauthorized access to the credentials.
Password protection: Implement limited access for authorized users.

Important
It's important to understand that the information above is not exhaustive, and as a developer, you hold the sole responsibility for ensuring the security of your application. We highly recommend further research on best security practices.

Sample configuration

// This is optional and only require to import private sheets
sheets({
    // Only necessary when dealing with private Google Sheets
    clientId: 'xyz.apps.googleusercontent.com',
    apiKey: 'xyzxyzxyz-q1',
    clientSecret: 'xyz-xyzxyz_xyz',
    redirectUri: 'http://localhost:8000/sheets',
    errorUri: 'http://localhost:8000/sheets/error',
});

// Import from a Google Sheets
sheets('https://docs.google.com/spreadsheets/d/16Es5bj0dSfPXDUsbDfdUc7Y5mHkIL8Skux5YJLOenV0/edit', {
    onload: function(title, config) {
        jspreadsheet(document.getElementById('spreadsheet'), config);
    }
});

Examples

Import from Google Sheets

Create a new JSS data grid from a remote public Google Sheets document. This example only works for public documents.

JavaScript example

<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/sheets/dist/index.min.js"></script>

<div id='spreadsheet'></div>

<input type="text" value="https://docs.google.com/spreadsheets/d/16Es5bj0dSfPXDUsbDfdUc7Y5mHkIL8Skux5YJLOenV0/edit" />
<input type='button' value='Import from Google Sheets' onclick='convert(this.previousElementSibling.value)'>

<script>
const convert = function(url) {
    jspreadsheet.sheets(url, {
        onload: function(title, config) {
            // Destroy any existing data grid
            jspreadsheet.destroy(document.getElementById('spreadsheet'));
            // Create a new data grid from the import process
            jspreadsheet(document.getElementById('spreadsheet'), config);
        }
    })
}

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTEzZGRjNTk2ZTc5MTZlMjc4NDRlMmNhNTJhYmE0MDZjOGE4ZWRkYTlkMjE5MzhiOGIyNzA2OWZkMDAxYjIzYTUxYzM3YzNkOGIzN2ZmY2FkNDZlYzJjZmRkYjhhYzU4NjAyOTRiNmRjMGYyMmU1NTI4NGNiYzlmY2ZkN2EyZDYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RVME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// Add-on for Spreadsheet
jspreadsheet.setExtensions({ sheets });
</script>
</html>

React example

import React, { useRef } from "react";
import jspreadsheet from "jspreadsheet";
import sheets from "@jspreadsheet/sheets";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTEzZGRjNTk2ZTc5MTZlMjc4NDRlMmNhNTJhYmE0MDZjOGE4ZWRkYTlkMjE5MzhiOGIyNzA2OWZkMDAxYjIzYTUxYzM3YzNkOGIzN2ZmY2FkNDZlYzJjZmRkYjhhYzU4NjAyOTRiNmRjMGYyMmU1NTI4NGNiYzlmY2ZkN2EyZDYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RVME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// Add-on for your JSS data grid
jspreadsheet.setExtensions({ sheets });

export default function App() {
    const input = useRef();
    const spreadsheet = useRef();

    const download = () => {
        jspreadsheet.sheets(document.getElementById('spreadsheet'), {
            onload: function(title, config) {
                jspreadsheet.sheets(spreadsheet.current, config);
            }
        });
    }

    return (
        <>
            <div ref={spreadsheet}></div>
            <input ref={input} type="text"
                value="https://docs.google.com/spreadsheets/d/16Es5bj0dSfPXDUsbDfdUc7Y5mHkIL8Skux5YJLOenV0/edit" />
            <input type="button" value="Import from Google Sheets" onClick={() => convert(input.current.value)} />
        </>
    );
}

Vue example

<template>
    <div ref="spreadsheet"></div>
    <input ref="input" type="text" value="https://docs.google.com/spreadsheets/d/16Es5bj0dSfPXDUsbDfdUc7Y5mHkIL8Skux5YJLOenV0/edit" />
    <input type="button" value="Import from Google Sheets" @click="convert" />
</template>

<script>
import { jspreadsheet } from "@jspreadsheet/vue";
import render from "@jspreadsheet/sheets";

// Define the data grid license
jspreadsheet.setLicense('YTEzZGRjNTk2ZTc5MTZlMjc4NDRlMmNhNTJhYmE0MDZjOGE4ZWRkYTlkMjE5MzhiOGIyNzA2OWZkMDAxYjIzYTUxYzM3YzNkOGIzN2ZmY2FkNDZlYzJjZmRkYjhhYzU4NjAyOTRiNmRjMGYyMmU1NTI4NGNiYzlmY2ZkN2EyZDYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RVME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// Define the data grid extensions
jspreadsheet.setLicense({ sheets });

export default {
    methods: {
        convert() {
            // Spreadsheet instance
            jspreadsheet.sheets(this.$refs.input.current, {
                onload: function(title, config) {
                    jspreadsheet(this.$refs.spreadsheet.current, options);
                }
            });
        }
    },
    data() {
        const input = ref(null);
        const spreadsheet = ref(null);

        return {
            spreadsheet,
            input
        };
    }
}
</script>

Angular example

import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import * as render from "@jspreadsheet/sheets";

import "/node_modules/jsuites/dist/jsuites.css";
import "/node_modules/jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTEzZGRjNTk2ZTc5MTZlMjc4NDRlMmNhNTJhYmE0MDZjOGE4ZWRkYTlkMjE5MzhiOGIyNzA2OWZkMDAxYjIzYTUxYzM3YzNkOGIzN2ZmY2FkNDZlYzJjZmRkYjhhYzU4NjAyOTRiNmRjMGYyMmU1NTI4NGNiYzlmY2ZkN2EyZDYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RVME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// Extensions
jspreadsheet.setExtensions({ sheets });

@Component({
  selector: "app-root",
  template: `<div #spreadsheet></div>
    <input #input type="text" value="https://docs.google.com/spreadsheets/d/16Es5bj0dSfPXDUsbDfdUc7Y5mHkIL8Skux5YJLOenV0/edit" />
    <input type="button" value="Import from Google Sheets" (click)="this.convert()" />`
})
export class AppComponent {
    @ViewChild("input") input: ElementRef;
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    export() {
        jspreadsheet.sheets(this.input.value, {
            onload: function(title, config) {
                jspreadsheet(this.spreadsheet, options);
            }
        });
    }
}