linglung

JSON Puller - Google Apps Script

Transforms the data of a given Spreadsheet Sheet to JSON.

• The frozen rows are taken as keys for the JSON.
• The data taken for the values is only that after the frozen rows

Set up:

exportJSON(Spreadsheet) - transforms the data in the given sheet to JSON.

linglung

function getDataFromXpath(path, url) {
  var data = UrlFetchApp.fetch(url);
  var text = data.getContentText();
  var xmlDoc = Xml.parse(text, true);
  
  // Replacing tbody tag because app script doesnt understand.
  path = path.replace("/html/","").replace("/tbody","","g");
  var tags = path.split("/");
  Logger.log("tags : " + tags);
  // getting the DOM of HTML

linglung

/*
PARAMS:
text    XML (In this gist, namespace needs to be 'http://www.w3.org/1999/xhtml')
path    XPATH (So far tested with simple indexed lookup /html/body/table[3])

RETURNS:
Array[][] (Table data)

Missing th/thead/tfoot support
*/

linglung

// instead of var results = YouTubeAnalytics.Reports.query(ids, start-date, end-date, metrics, optionalArgs);
var params = {"method" : "post",
              "payload" : {'ids' : query.ids,
                           "startDate" : startDate,
                           "endDate": endDate,
                           "metrics": query.metrics,
                           "options": JSON.stringify(options)
                          }
             };
var yt_data = UrlFetchApp.fetch("https://script.google.com/macros/s/YOUR_APP_ID/exec", params);

linglung

function convertSheet2Json(sheet) {
  // first line(title)
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {

linglung

Telegram Bot on Google App Script

This is the source code of one of my blog post. To read the full blog post please click here.

linglung

CLEAN method for Google Apps Script

This is a sample script which works the same action with the CLEAN method of VBA. The CLEAN method of VBA removes the characters of 0-31, 127, 129, 141, 143, 144, 157. Although I had looked for such method for Google Apps Script, I couldn't find it. So I created this. If this is useful for you, I'm glad.

function cleanForGAS(str) {
  if (typeof str == "string") {
    var escaped = escape(str.trim());
    for (var i = 0; i <= 31; i++) {
      var s = i.toString(16);
 var re = new RegExp("%" + (s.length == 1 ? "0" + s : s).toUpperCase(), "g");

linglung

/**
 * Google Script - Add Fonts.
 * Add other fonts in Google Spreadsheet.
 *
 * a) Open 'Script editor'
 * b) Add this script
 * c) Save and run 'onOpen'
 * d) Go to some 'Spreadsheet' project
 * e) Click to 'Font Editor' and then to 'Change Font'
 */

linglung

function columnToLetter(column, row) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter + row;
}

linglung

function getJSON(aUrl,sheetname) {
  //var sheetname = "test";
  //var aUrl = "http://pipes.yahoo.com/pipes/pipe.run?_id=286bbb1d8d30f65b54173b3b752fa4d9&_render=json";
  var response = UrlFetchApp.fetch(aUrl); // get feed
  var content = JSON.parse(response.getContentText()); //
  var data = content.value.items;
  for (i in data){
    data[i].pubDate = new Date(data[i].pubDate);
    data[i].start = data[i].pubDate;
  }