[GAS] Get Json File Data into Google Sheet :

SOURCE LEARNING :
https://www.youtube.com/watch?v=EXKhVQU37WM

Learn how to import JSON data from API to Google Sheets using user defined IMPORTJSON function. Get the function script here https://www.chicagocomputerclasses.co...

DEMO JSON FILE :

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

(1) =importjson("URL","menu/popup/menuitem/0")

OUTPUT :

value
New
onclick
CreateNewDoc()

(2) =importjson("URL","menu/popup/menuitem/2")
OUTPUT :

value
Close
onclick
CloseDoc()

(3) =importjson("URL","menu")
OUTPUT :

id
file
value
File
popup

it's work .

The (1) & (2) are array

[GAS][Json] Get Json Data into Webpage (建築中)

Get Json Data into Webpage

SOURCE : https://www.udemy.com/google-script/learn/v4/t/lecture/7465660?start=0

(1) 最初的低級錯誤 , 未 PUBLISH "SHEET" , 所以取不到DATA

(2) 在LOCAL BROSWER 接受 中文字的 變數 ('排名'), 但 放在寄存DOMAIN 上 , 不接受中文變數名稱

(3) 變數 及 指令 是大細階 要清楚的 

----> CODE OF LOCAL WEBPAGE

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/css/bootstrap.min.css" integrity="sha384-rwoIResjU2yc3z8GV/NPeZWAv56rSmLldC3R/AZzGRnGxQQKnKkoFVhFQhNUwEyJ" crossorigin="anonymous">
<div>
<h1> Pin Killer Ranking </h1>
    <table id="myTable" class="table table-striped">
        <tr>
            <th>Rank</th>
            <th>Name</th>
            <th>Avg</th>
<th>受讓</th>
        </tr>
    </table>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js" integrity="sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/js/bootstrap.min.js" integrity="sha384-vBWWzlZJ8ea9aCX4pEW3rVHjgjt7zpkNpZk+02D9phzyeVkE+jo0ieGizqPLForn" crossorigin="anonymous"></script>
<script>
    $.getJSON('https://spreadsheets.google.com/feeds/list/11uGtICNHXAmXQ_lkLwubiNkVMdBX9rWf3U1KPra257k/od6/public/values?alt=json', function(data){
var d = data.feed.entry;
        jQuery.each(d, function () {
     var x = Math.floor((200-this['gsx$avg']['$t'])*0.8);
            $('#myTable tr:last').after('<tr><td>' + this['gsx$排名']['$t'] + '</td><td>' + this['gsx$name']['$t'] + '</td><td>' + this['gsx$avg']['$t'] + '</td><td>' + x + '</td></tr>');
        })
     console.log(data);
})
     
</script>

[GAS] Use Sheets Info make Json URL link

(1) CREATE A GOOGLE SHEETS FILE

i.e. https://docs.google.com/spreadsheets/d/17heQ3qPjPvkv8jNmnGRsoBJZHwm70XZvJtUfPbuiMd4/edit#gid=0

(2) Get the id

17heQ3qPjPvkv8jNmnGRsoBJZHwm70XZvJtUfPbuiMd4

(3) Publish to the web

       Select  the "sheet" to publish

(4) make the Json file URL link (use the id) :

https://spreadsheets.google.com/feeds/list/************/od6/public/values?alt=json

i.e.

https://spreadsheets.google.com/feeds/list/17heQ3qPjPvkv8jNmnGRsoBJZHwm70XZvJtUfPbuiMd4/od6/public/values?alt=json

[GAS] Google Script Resources

Google Script Resources

第 1 節，12 講座

Create a Google Account

https://accounts.google.com/SignUp

Connected Apps Script

https://myaccount.google.com/permissions?pli=1

Google Apps Script Reference Guide

https://developers.google.com/apps-script/reference/document/

Apps Script Main Page

https://developers.google.com/apps-script/

Create Apps Script

https://www.google.com/script/start/

[GAS] 用GMAIL發電郵過知自己文件資料

用GMAIL發電郵過知自己文件資料

function myFunction4(){
  var myDoc = DocumentApp.openById('1ecB4hbtm5-MC59cTap2EZ46eEPRsN8WHpajjBWd4UIU');
  var body = myDoc.getBody();
  var email = Session.getActiveUser().getEmail();
  var docID = myDoc.getId();
  var docName = myDoc.getName();
  var docURL = myDoc.getUrl();
  var emailBody = 'This is the new doc info from ' + docName + ' with ID ' + docID + '\n Click here > '+docURL; ;
  GmailApp.sendEmail(email, 'New Doc info', emailBody);
  
}

[GAS] 用Google Translate 將英文轉做西班牙文

用Google Translate 將英文轉做西班牙文

*裝空行跳過不處理

function myFunction3(){
  var myDoc = DocumentApp.openById('1ecB4hbtm5-MC59cTap2EZ46eEPRsN8WHpajjBWd4UIU');
  var body = myDoc.getBody();
  var holder = "NEW OUTPUT\n";
  for(x=0;x<body.getNumChildren();x++){
    var english = body.getChild(x).getText();
    if(english.length >0 ){
    var spanish = LanguageApp.translate(english, 'en', 'es');
    holder +=  english + ' = ' +  spanish + '\n';
    }
  }
 
}

[GAS] 開啟文件,將預設的格式,逐一套落文件中的每一項(CHILD)並加在文件中的尾部

開啟文件,將預設的格式,逐一套落文件中的每一項(CHILD)並加在文件中的尾部

function myFunction2(){
  var myDoc = DocumentApp.openById('1ecB4hbtm5-MC59cTap2EZ46eEPRsN8WHpajjBWd4UIU');
  var body = myDoc.getBody();
  var holder = "NEW OUTPUT";
  var style = {};
  style[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
  style[DocumentApp.Attribute.BOLD] = true;  
  style[DocumentApp.Attribute.FONT_SIZE] = 24;
  style[DocumentApp.Attribute.BACKGROUND_COLOR] = '#FF0000';
  style[DocumentApp.Attribute.FOREGROUND_COLOR]= '#FFFFFF';
  for(x=0;x<body.getNumChildren();x++){
    holder += x + '. ' + body.getChild(x).getText() + '\n' ;
  }

[GAS] 呼叫一個已存在的Google Doc並在文件最後加上文字及現在日期及時間

呼叫一個已存在的Google Doc並在文件最後加上文字及現在日期及時間

function myFunction1() {
  var myDoc = DocumentApp.openById('1ecB4hbtm5-MC59cTap2EZ46eEPRsN8WHpajjBWd4UIU');
  var body = myDoc.getBody();
  
  var curDate = new Date();
  body.appendParagraph("New content added !!!!" + curDate.toLocaleString());
}

[GAS] 造一個新Google Doc 並在文章尾部加文字

 造一個新Google Doc 並在文章尾部加文字

 function myFunction() {
  var myDoc = DocumentApp.create('My Test Doc 4');
  var body = myDoc.getBody();
  body.appendParagraph("Adding new content to the document");
}