Retrieve Data From Google Scripts

Question

I wrote a custom Google Script which outputs an object for me and I would like to be able to call it and assign it to a variable which is then used to display data on a website.

Currently, I have figured out to access the data using another Google Scripts project, however I can't find a way to do it using something like a JS file on my computer or DreamHost server.

This is what I have tried:

var infected_data = getData();

function getData() {
  var URL = 'https://script.google.com/macros/s/AKfycbzsyQNJwDvQc5SvNGEDZZOoNI3XxNar9PA9sRucZx7mgzfWpFQ/exec';

  var response = UrlFetchApp.fetch(URL);

 return response;
}

on something like playcode.io it gives me the following error:

error: Uncaught ReferenceError: UrlFetchApp is not defined

I get the same thing just putting it on my computer and running it there with Chrome:

It appears UrlFetchApp is only for within Google Scripts. Is there some other way to access the output outside of the Google environment.

*I don't know how authorization and all that fits in here (because Google Scripts seems to run it as my user. right?)

HTML Header:

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
    <title>JQVMap - World Map</title>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
    <link href="../dist/jqvmap.css" media="screen" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
    <script type="text/javascript" src="../dist/jquery.vmap.js"></script>
    <script type="text/javascript" src="../dist/maps/jquery.vmap.world.js" charset="utf-8"></script>
    <script type="text/javascript" src="js/jquery.vmap.sampledata.deaths.js"></script>
    <script type="text/javascript" src="js/jquery.vmap.sampledata.infected.js"></script>
    <script>
        jQuery(document).ready(function () {
            jQuery('#vmap').vectorMap({
                map: 'world_en',
                backgroundColor: '#333333',
                color: '#ffffff',
                hoverOpacity: 0.8,
                selectedColor: '#3498DB',
                enableZoom: true,
                showTooltip: true,
                scaleColors: ['#F3A291', '#FF4F3B'],
                values: infected_data,
                normalizeFunction: 'polynomial',
                onLabelShow: function (event, label, code) {
                    label.html('<div class="map-tooltip"><h1 class="header"> ' + label.html() + '</h1><p class="description">Infected: ' + infected_data[code] + '</p><p class="description">Deaths: ' + death_data[code] + '</p></div>');
                }
            });
        });
    </script>
</head>

Google Scripts File:

function doGet() {
    var result = {};
    var infected = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getDataRange().getValues();
    var death = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getDataRange().getValues();
    result = makeObject(infected);

    return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

function makeObject(multiArr) {
    var obj = {};
    var countrystats = {};
    var headers = multiArr.shift();

    for (var i = 0; i < headers.length; i++) {
        countrystats[i] = multiArr.map(function (app) {
            return app[i];
        })
    }

    for (var m = 0; m < countrystats[1].length; m++) {
        obj[countrystats[1][m]] = 0;
    }

    for (var j = 0; j < countrystats[1].length; j++) {
        var TempVar;
        TempVar = obj[countrystats[1][j]];
        obj[countrystats[1][j]] = TempVar + countrystats[3][j];

    }

    return obj;
}

Google Scripts Output (using the JSON View chrome extension):

{
cn: 8134,
th: 23,
mo: 7,
us: 5,
jp: 11,
kr: 4,
sg: 10,
vn: 2,
fr: 5,
np: 1,
my: 8,
ca: 3,
ci: 1,
lk: 1,
au: 9,
de: 4,
fn: 1
}

This is a public link with the object/data i want on it (the same object shown above):web app: https://script.google.com/macros/s/AKfycbzsyQNJwDvQc5SvNGEDZZOoNI3XxNar9PA9sRucZx7mgzfWpFQ/exec

So basically anyone who uses it should be able to access it. I just need a way to assign that data to a local JS variable. The google sheets script is published as a web app. If I'm not mistaken there is a setting to allow anyone, even anonymous to access it.

Here is my attempt at an AJAX request: var url = "https://script.google.com/macros/s/AKfycbzsyQNJwDvQc5SvNGEDZZOoNI3XxNar9PA9sRucZx7mgzfWpFQ/exec";

  var infected_data = jQuery.ajax({
    crossDomain: true,
    url: url,
    method: "GET",
    //dataType: "jsonp"
  });

If i uncomment the jsonp I get an error:

jquery-1.11.3.min.js:5 Cross-Origin Read Blocking (CORB) blocked cross-origin response https://script.googleusercontent.com/macros/echo?user_content_key=JXkCjiJjhcjndRREjoGyVNkZNkD-HvKpEPkpicQBm9nR9OkxjGXdYuOPsLxbJf-B9Rgifl5NWMtzgjfVGuMdGxTJrjKnRpdcOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHazTNYZyoqG0ZuVXpWSNdoeLErB4AfUCNPKJHgELe5WaAmN5SlwIhonlWkkbFzR8kUwjKrMtdq9u-YqreD7W_KJ_aVqKVBTehAuogPCoZCfVc4yJf5ieDCdMDbXQ8FZZq8iSedsk1Px1LnPBLM8W-ZRcknnbJNT8dS525XG1pNEBR&lib=Mw_Scq3iKhByBS86NJpd_CngcdEShCw7K with MIME type application/json. See https://www.chromestatus.com/feature/5629709824032768 for more details.

I don't get any errors if i remove it. However, i still can't see the data on my interactive map (My application).

thank you for your helpful insight. Right now I have a bunch of website files sitting in a folder on my desktop. The HTML file has a script in its header which calls a java script file with just an object inside of it lined to a variable. (Var TestObject = {Item1, Item2, Item3, Item4....}. The google script returns that object. So if gives you any more information, i just need to get that object from google scrips to my java script variable locally. — CWrecker, Jan 30 '20 at 19:34
These files will then be uploaded to a dream host server to be hosted, if that changes anything. I'll look into the GET request. in the mean time if you have any other information, that would be great. Thanks! :) — CWrecker, Jan 30 '20 at 19:36
Question updated. I am using JQVMaps. (GitHub can be found here: https://github.com/10bestdesign/jqvmap) The infected_data object (This is what is being produced by my google script) is stored in this JS file: "js/jquery.vmap.sampledata.infected.js". Similar for the death_data in the "js/jquery.vmap.sampledata.deaths.js" file — CWrecker, Jan 30 '20 at 19:44
Please show JSON and other text-based output as text, not as image of text. Also, there is no need to put "Update:" in front of everything; we can see the entire history of the edits made to the question if we want to know what's new. — Heretic Monkey, Jan 30 '20 at 19:55
cool, thanks for the tips. Still getting the hang things around here. :) — CWrecker, Jan 30 '20 at 20:07
I've deleted my previous comments because they are now unneeded, and edited/added the tags to include jQuery so that someone with jQuery experience will see your question in a search. — Alan Wells, Jan 30 '20 at 21:20
@Camden S Can I ask you about your question? 1. What is the top of script of your question? 2. Does the function `makeObject` at Google Apps Script returns an object of the bottom of your question? 3. Can I ask you about the goal of your this question? — Tanaike, Jan 31 '20 at 00:03
Ya sure: 1) The first script was simply my attempt to get data from one google script to another. The problem with this is that it only works using google scrips, not any other environment. 2)The make Object does return a function, the output shown in the last part of the question. (JSON View) 3) the goal is to get live data from a google sheets document and display it on a website. — CWrecker, Jan 31 '20 at 07:33
1. Where is the website? hosted on Script.google.com or other website? 2. How is the `doGet()` web-app published-access/ execute as? — TheMaster, Jan 31 '20 at 09:47

score 1 · Answer 1 · answered Jan 31 '20 at 00:21

1

If you are trying to get data from a Google Spreadsheet from outside of Google Apps Script, like on your own website/server, you will need to use the Sheets API.

Here is a good article on how to make Google API calls using JavaScript: https://medium.com/google-cloud/gapi-the-google-apis-client-library-for-browser-javascript-5896b12dbbd5.

answered Jan 31 '20 at 00:21

IMTheNachoMan

5,343
5
40
89

This seems rather complicated for my use. Is there a simpler way to just assign the output of a google script to a variable? – CWrecker Jan 31 '20 at 07:58
So i have been messing around with the Google API and it seems messy. the google Apps Script i wrote is closely linked to a google sheet file (this is where it gets its data from). If i am to use google scripts API then i need to make it a standard project, which seems like it will then unlink the sheets document. Maybe you can clear a few things up and point me in the right direction. thanks for the help, i'll keep looking into it until i find another solution. Should be simple to just access a apps script, i don't need authorization, that makes it too clunky. – CWrecker Jan 31 '20 at 08:53
This is a public link with the object/data i want on it: https://script.googleusercontent.com/macros/echo?user_content_key=yv_df8uWw_A-nPdEbxvA-18cV3jeIwA_I4zt8_e6e7e8xOIyJhaDNZFVsh3rrF03-2FUcd2huVCnDC43Gu-9dOR9RHo7vWBem5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnNME_LkxuEc29S38Zvu2UEDOdU81AhqyKUtyuSveZcI5RRtKEjnF5sdFghBl97br2w&lib=Mw_Scq3iKhByBS86NJpd_CngcdEShCw7K So basically anyone who uses it should be able to access it. I just need a way to assign that data to a local JS variable. The google sheets script is published as a web app. – CWrecker Jan 31 '20 at 08:55
When you say "assign that data to a local JS variable" what do you mean? What is "local" in this context? It sounds like you just want to make an AJAX web request using JavaScript? https://www.freecodecamp.org/news/here-is-the-most-popular-ways-to-make-an-http-request-in-javascript-954ce8c95aaa/ – IMTheNachoMan Jan 31 '20 at 18:23
I guess local for me is the files where my website lives. Like index.html all my other JS. I just need to get the object from google scripts to a JS file on my computer/server. – CWrecker Feb 01 '20 at 09:53
1

So you already have a GAS web-app that'll return JSON of your Sheet data. Now you just need to use AJAX from your local server to call your web-app URL and save the AJAX call return to a JS object. – IMTheNachoMan Feb 02 '20 at 05:59
Hey, I saw your request, I’ll add you so you can see if you can come up with anything. The problem is, will an AJAX request need to be authorized just like you do? Ok it says it is shared, even though it gives me an error each time I try to add you. – CWrecker Feb 02 '20 at 08:44
Take a look at the post again. I updated it. Let me know what you think – CWrecker Feb 02 '20 at 11:25

score 0 · Accepted Answer · answered Feb 09 '20 at 12:00

const url = "PUT YOUR GOOGLE WEB APP URL HERE";

// Declare an async function
  const getData = async () => {
  // Use the await keyword to let JS know this variable has some latency so it should wait for it to be filled 
  // When the variable is fetched, use the .then() callback to carry on 
    const DataJSON = await fetch(url).then(response => 
      response.json()
    )

    return await DataJSON
  };

Retrieve Data From Google Scripts

2 Answers2