MagicMirror Forum
    • Recent
    • Tags
    • Unsolved
    • Solved
    • MagicMirror² Repository
    • Documentation
    • 3rd-Party-Modules
    • Donate
    • Discord
    • Register
    • Login
    A New Chapter for MagicMirror: The Community Takes the Lead
    Read the statement by Michael Teeuw here.

    Updating Compliments using a spreadsheet

    Scheduled Pinned Locked Moved Development
    14 Posts 4 Posters 3.5k Views 4 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • B Offline
      bwente
      last edited by

      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.

      0_1502683309962_mirror_snap.JPG

      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.141S 1 Reply Last reply Reply Quote 1
      • strawberry 3.141S Offline
        strawberry 3.141 Project Sponsor Module Developer @bwente
        last edited by

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

        Please create a github issue if you need help, so I can keep track

        B 1 Reply Last reply Reply Quote 0
        • B Offline
          bwente @strawberry 3.141
          last edited by

          @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."
                  }
                }
              ]
            }
          }
          
          1 Reply Last reply Reply Quote 0
          • E Offline
            emrah_asl
            last edited by

            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 ?

            S 1 Reply Last reply Reply Quote 0
            • S Offline
              sdetweil @emrah_asl
              last edited by

              @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.

              Sam

              How to add modules

              learning how to use browser developers window for css changes

              1 Reply Last reply Reply Quote 0
              • E Offline
                emrah_asl
                last edited by 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”

                S 1 Reply Last reply Reply Quote 0
                • S Offline
                  sdetweil @emrah_asl
                  last edited by

                  @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.

                  Sam

                  How to add modules

                  learning how to use browser developers window for css changes

                  E 1 Reply Last reply Reply Quote 0
                  • E Offline
                    emrah_asl @sdetweil
                    last edited by

                    @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.

                    S 1 Reply Last reply Reply Quote 0
                    • S Offline
                      sdetweil @emrah_asl
                      last edited by

                      @emrah_asl yeh, that won’t work

                      Sam

                      How to add modules

                      learning how to use browser developers window for css changes

                      1 Reply Last reply Reply Quote 0
                      • E Offline
                        emrah_asl
                        last edited by

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

                        S 1 Reply Last reply Reply Quote 0
                        • 1
                        • 2
                        • 1 / 2
                        • First post
                          Last post
                        Enjoying MagicMirror? Please consider a donation!
                        MagicMirror created by Michael Teeuw.
                        Forum managed by Sam, technical setup by Karsten.
                        This forum is using NodeBB as its core | Contributors
                        Contact | Privacy Policy