Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Spreadsheet GET JSON Parser
var sheet = SpreadsheetApp.openById('YOUR_ID');
SpreadsheetApp.setActiveSpreadsheet(sheet);
/**
* @param currentSheet
* @param findKey
* @return {Array}
*/
function getStartEndRows(currentSheet, findKey) {
var data = currentSheet.getDataRange().getValues();
var values = [];
var found = false;
// Начало выборки
var startRow = 0;
var startNum = 0;
// Конец выборки
var endRow = 0;
var endNum = 0;
/**
* Сохраняем максимальное значение
* @return {Number}
*/
var setMaxNum = (function() {
var max = 0;
return function(num) {
if(num > max) {
max = num;
}
return max;
}
}());
for (var i = 0; i < data.length; i++) {
var row = data[i];
var rowIsEmpty = true;
for (var j = 0; j < row.length; j++) {
var value = data[i][j];
if(value.toString().trim() === findKey) {
found = true;
startRow = i;
startNum = j;
rowIsEmpty = false;
} else if(found && value !== '') {
endRow = i;
endNum = setMaxNum(j);
rowIsEmpty = false;
}
}
if(found && rowIsEmpty) {
break;
}
}
var dataRange = currentSheet.getRange(startRow + 1, startNum + 1, endRow + 1, endNum + 1);
if(found) {
values = dataRange.getValues();
}
return values;
}
/**
* Example: getValues('Количество начисляемой игроку ВВ', 'GameMode_Sprint');
* @param findKey поиск по ключу {String}
* @param sheetKey имя вкладки {String}
* @return {Object}
*/
function getValues(findKey, sheetKey) {
var sheets = sheet.getSheets();
var arrayData = [];
for (var i = 0; i < sheets.length; i++) {
var currentSheet = sheets[i];
var sheetName = currentSheet.getName();
if(sheetName === sheetKey) {
arrayData = getStartEndRows(currentSheet, findKey);
if(arrayData.length) {
break;
}
}
};
SpreadsheetApp.flush();
return arrayData;
}
function doGet(request) {
var bar = getValues('HEADER_TEXT', 'SHEET_NAME');
var output = JSON.stringify({
'foo' : bar
});
return ContentService
.createTextOutput(output)
.setMimeType(ContentService.MimeType.JSON);
}
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.