Some frequently used scripts powered by Google Apps Script

GAS Toolbox

This Gist is created by Katrina Chan ([email protected]), 
for storing some frequently used scripts when developing Google Apps Script project.
Licensed under MIT.

[Updates]
2016/12/30 update googl.gs, sheet2Arr.gs
2018/3/7 update sheet2JSONsrv.gs

googl.gs

/**
 * Shorten URLs to make them easier to share
 *
 * @param {string} input The long URL for shortening
 */
function googl (longUrl) {
  var req = {
    'longUrl' : longUrl
  }
  var res = UrlFetchApp.fetch('https://www.googleapis.com/urlshortener/v1/url?key={{API_KEY}}', {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(req)
  });
  if (res.getResponseCode() < 400) {
    var par = JSON.parse(res.getContentText());
    return par.id;
  } else {
    return res.getContentText();
  }
}

sheet2Arr.gs

function sheet2Arr (SpreadsheetId, SheetName) {
  var spreadsheet = SpreadsheetApp.openById(SpreadsheetId);
  var sheet = spreadsheet.getSheetByName(SheetName);

  var items = sheet.getRange('1:1').getValues();
  var names = sheet.getRange('A:A').getValues();

  var data = [];

  for (var row = 1; row < names.length; ++row) {
    var rowObj = {};

    for (var col = 0; col < items[0].length; ++col) {
      var item = items[0][col];
      rowObj[item] = sheet.getRange(row+1, col+1);
    }

    data.push(rowObj);
  }
  
  return data;
}

sheet2JSONsrv.gs

function doGet(e) {
  var id = 'SPREADSHEET_ID';
  var table = 'TABLE_NAME';
  
  // for treating each row as a single object
  // [{...}, {...}, {...}]
  var data = sheet2ArrStatic(id, table);
  
  // for treating first column as the key of each row
  // {'___': {...}, '___': {...}, '___': {...}}
  // var data = sheet2ObjStatic(id, table);
  
  var output = ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  return output;
}

function sheet2ArrStatic(id, name) {
  var spreadsheet = SpreadsheetApp.openById(id);
  var sheet = spreadsheet.getSheetByName(name);

  var item_range = sheet.getRange('1:1').getValues();
  var key_range = sheet.getRange('A2:A').getValues();
  
  var items = [];
  var keys = [];
  
  for (var idx in item_range[0]) {
    if (item_range[0][idx] == '') {
      break;
    }
    items.push(item_range[0][idx]);
  }
  
  for (var idx in key_range) {
    if (key_range[idx] == '') {
      break;
    }
    keys.push(key_range[idx]);
  }
  
  var row_number = keys.length;
  var column_number = items.length;
  
  var range = sheet.getRange(2,1,row_number,column_number);
  
  var data = [];
  
  var values = range.getValues();
  
  for (var row = 0; row < row_number; ++row) {
    var row_object = {};

    for (var col = 0; col < column_number; ++col) {
      var item = items[col];
      row_object[item] = values[row][col];
    }

    data.push(row_object);
  }
  return data;
}

function sheet2ObjStatic(id, name) {
  var spreadsheet = SpreadsheetApp.openById(id);
  var sheet = spreadsheet.getSheetByName(name);

  var item_range = sheet.getRange('1:1').getValues();
  var key_range = sheet.getRange('A2:A').getValues();
  
  var items = [];
  var keys = [];
  
  for (var idx in item_range[0]) {
    if (item_range[0][idx] == '') {
      break;
    }
    items.push(item_range[0][idx]);
  }
  
  for (var idx in key_range) {
    if (key_range[idx] == '') {
      break;
    }
    keys.push(key_range[idx]);
  }
  
  var row_number = keys.length;
  var column_number = items.length;
  
  var range = sheet.getRange(2,1,row_number,column_number);
  
  var data = {};
  
  var values = range.getValues();
  
  for (var row = 0; row < row_number; ++row) {
    var row_object = {};

    for (var col = 1; col < column_number; ++col) {
      var item = items[col];
      row_object[item] = values[row][col];
    }

    data[keys[row]] = row_object;
  }
  return data;
}