Skip to content

Instantly share code, notes, and snippets.

@katrina376 katrina376/GAS Toolbox
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
You can’t perform that action at this time.