Updating Compliments using a spreadsheet

bwente

I just discovered MagicMirror last week and got things setup this weekend. It has been a blast trying out new modules. A big thank you to everyone in the community that has given their time.

I am not a programmer and not much for command line. But I can follow instructions and copy and paste. I was looking for ways to update my mirror without having to touch terminal. I wanted an easy way to update my Compliments without making an JSON file by hand. I setup a new google sheets document.

0_1502682132337_6dd2e42c-6148-44eb-907a-42fd5bace095-image.png

Then published it.

0_1502682216393_2dd17c70-16a4-4c55-b7f7-7f165b581226-image.png

It turns out the JSON that google exports is not the JSON that Compliments needs. I tired my hand at jquery and html and got it to create the same format in the browser. But what is rendered in the browser window isn’t JSON either, it looks like it but it isn’t. I tried specifying the remotefile path as the html file that I made didn’t work.

I then tried writing to the file system and javascript can’t do that in the browser either. Then I discovered that node.js can write a file. I converted my jquery JSON looper into a node file.

I started this morning with my first javascript, and this afternoon my first node.js, now tonight I am trying to figure out how to best make it work. I have to manually run

node remotefile.js

config.js

{
     module: 'compliments',
     position: 'lower_third',
     config: {
	 remoteFile: 'remotefile.json'               
     }
}

And finally my noob code…

'use strict';
var request = require('request');

var url = 'https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values?alt=json';
var compliments = '';
var anytime = '';
var morning = '';
var afternoon = '';
var evening = '';

request.get({
    url: url,
    json: true,
    headers: { 'User-Agent': 'request' }
}, (err, res, data) => {
    if (err) {
        console.log('Error:', err);
    } else if (res.statusCode !== 200) {
        console.log('Status:', res.statusCode);
    } else {
        // data is already parsed as JSON:
        var entry = data.feed.entry;

        // anytime column
        anytime += '\t"anytime" : [\r\n';
        for (var i = 0; i < entry.length; i++) {
            if (entry[i]['gsx$anytime']['$t']) {
                anytime += '\t\t"' + entry[i]['gsx$anytime']['$t'] + '",\r\n';
            }
        }
        anytime = anytime.replace(/,\s*$/, "") + '\r\n';
        anytime += '\t],\r\n';

        // morning column
        morning += '\t"morning" : [\r\n';
        for (var i = 0; i < entry.length; i++) {
            if (entry[i]['gsx$morning']['$t']) {
                morning += '\t\t"' + entry[i]['gsx$morning']['$t'] + '",\r\n';
            }
        }
        morning = morning.replace(/,\s*$/, "") + '\r\n';
        morning += '\t],\r\n';

        // afternoon column
        afternoon += '\t"afternoon" : [\r\n';
        for (var i = 0; i < entry.length; i++) {
            if (entry[i]['gsx$afternoon']['$t']) {
                afternoon += '\t\t"' + entry[i]['gsx$afternoon']['$t'] + '",\r\n';
            }
        }
        afternoon = afternoon.replace(/,\s*$/, "") + '\r\n';
        afternoon += '\t],\r\n';

        // evening column
        evening += '\t"evening" : [\r\n';
        for (var i = 0; i < entry.length; i++) {
            if (entry[i]['gsx$evening']['$t']) {
                evening += '\t\t"' + entry[i]['gsx$evening']['$t'] + '",\r\n';
            }
        }
        evening = evening.replace(/,\s*$/, "") + '\r\n';
        evening += '\t]';


        compliments = '{\r\n' + anytime + morning + afternoon + evening + '\r\n}';

        var fs = require('fs');
        fs.writeFile("remotefile.json", compliments, function(err) {
            if (err) {
                return console.log(err);
            }

            console.log("The file was saved!");
        });

    }
});

I know the code could be a lot better. I also know I could use PHP to make it easier (just return JSON from the url). What would be the best MagicMirror way?

strawberry 3.141

@bwente can you post the data structure you are receiving from spreadsheet?

bwente

@strawberry-3.141

Google being very through with their JSON…

{
  "version": "1.0",
  "encoding": "UTF-8",
  "feed": {
    "xmlns": "http://www.w3.org/2005/Atom",
    "xmlns$openSearch": "http://a9.com/-/spec/opensearchrss/1.0/",
    "xmlns$gsx": "http://schemas.google.com/spreadsheets/2006/extended",
    "id": {
      "$t": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values"
    },
    "updated": {
      "$t": "2017-08-14T03:28:11.565Z"
    },
    "category": [
      {
        "scheme": "http://schemas.google.com/spreadsheets/2006",
        "term": "http://schemas.google.com/spreadsheets/2006#list"
      }
    ],
    "title": {
      "type": "text",
      "$t": "Sheet1"
    },
    "link": [
      {
        "rel": "alternate",
        "type": "application/atom+xml",
        "href": "https://docs.google.com/spreadsheets/d/YOUR_SECRET_SHEET_LINK_ID/pubhtml"
      },
      {
        "rel": "http://schemas.google.com/g/2005#feed",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values"
      },
      {
        "rel": "http://schemas.google.com/g/2005#post",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values"
      },
      {
        "rel": "self",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values?alt=json"
      }
    ],
    "author": [
      {
        "name": {
          "$t": "bwente"
        },
        "email": {
          "$t": "bwente@email.com"
        }
      }
    ],
    "openSearch$totalResults": {
      "$t": "3"
    },
    "openSearch$startIndex": {
      "$t": "1"
    },
    "entry": [
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cokwr"
        },
        "updated": {
          "$t": "2017-08-14T03:28:11.565Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "How you doing?"
        },
        "content": {
          "type": "text",
          "$t": "morning: Good morning, sunshine!, afternoon: Hitting your stride!, evening: You made someone smile today, I know it."
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cokwr"
          }
        ],
        "gsx$anytime": {
          "$t": "How you doing?"
        },
        "gsx$morning": {
          "$t": "Good morning, sunshine!"
        },
        "gsx$afternoon": {
          "$t": "Hitting your stride!"
        },
        "gsx$evening": {
          "$t": "You made someone smile today, I know it."
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cpzh4"
        },
        "updated": {
          "$t": "2017-08-14T03:28:11.565Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Row: 3"
        },
        "content": {
          "type": "text",
          "$t": "morning: Who needs coffee when you have your smile?, afternoon: You are making a difference!, evening: You are making a difference."
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cpzh4"
          }
        ],
        "gsx$anytime": {
          "$t": ""
        },
        "gsx$morning": {
          "$t": "Who needs coffee when you have your smile?"
        },
        "gsx$afternoon": {
          "$t": "You are making a difference!"
        },
        "gsx$evening": {
          "$t": "You are making a difference."
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cre1l"
        },
        "updated": {
          "$t": "2017-08-14T03:28:11.565Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Row: 4"
        },
        "content": {
          "type": "text",
          "$t": "morning: Go get 'em, Tiger!, afternoon: You're more fun than bubble wrap!, evening: The day was better for your efforts."
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/YOUR_SECRET_SHEET_LINK_ID/od6/public/values/cre1l"
          }
        ],
        "gsx$anytime": {
          "$t": ""
        },
        "gsx$morning": {
          "$t": "Go get 'em, Tiger!"
        },
        "gsx$afternoon": {
          "$t": "You're more fun than bubble wrap!"
        },
        "gsx$evening": {
          "$t": "The day was better for your efforts."
        }
      }
    ]
  }
}