Skip to content

Instantly share code, notes, and snippets.

@katrina376
Last active Mar 24, 2018
Embed
What would you like to do?
Some frequently used scripts powered by Google Apps Script
This Gist is created by Katrina Chan (katrina.hyc@gmail.com),
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
/**
* 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();
}
}
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;
}
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment