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."
        }
      }
    ]
  }
}

emrah_asl

Hi, where did you write this code ? When I try write on “compliments.js” my console does not recognize ‘require’ tag. Could you please share fullcode ?

sdetweil

@emrah_asl the modulename.js does not allow require(). as it runs inside the browser. anything needing require() must be done in the node_helper.js, and the two parts must send data back and forth with socketNotifications per the mm spec.

emrah_asl

Hi @sdetweil thanks I missed up

, so what should I customize compliment.js ?

Here is my change on compliement.js

remoteFile: "remotefile.json",

I created my json link and create node_helper.js file and put my folder with these codes.

But I think I missed somethings, it doesn’t work, and there is no file creation on folder called “remotefile.json”

sdetweil

@emrah_asl well, what does the node_helper do? there is a specific structure to that file, as it is a program under mm as part of a module. if u created it as a standalone pgm, then it won’t work like u want.

emrah_asl

@sdetweil after I created node__helper.js manually, then I typed ‘npm start’ , the file has created, bu this time there was electron error (MM closed) interesting.

sdetweil

@emrah_asl yeh, that won’t work

emrah_asl

@sdetweil so, what is formula to run this ? what do you think acording your experience ?