小贝子编程

如何在Google Apps脚本中从压缩的blob中恢复JSON字符串



我需要将一个大型json存储到电子表格的单元格中。由于有50k个字符的限制，我考虑压缩json字符串。我已经设法将压缩的blob存储为base64编码的字符串，但未能将其恢复为原始json。当调用readLargeJson函数时，我得到以下错误："Blob对象必须具有非null内容类型才能执行此操作"。

下面的insertLargeJson函数似乎工作正常。

我也尝试过在不使用base64编码的情况下进行存储，但没有帮助。

function insertLargeJson()
{
var obj = {};
obj["dummy"] = [];
// Just to make the json huge
for (var i = 0; i < 10000; i++)
{
obj["dummy"].push("value");
}
var activeSheet = SpreadsheetApp.getActiveSheet();
var str = JSON.stringify(obj);
var blob = Utilities.newBlob(str, 'application/octet-stream');
var compressedBlob = Utilities.zip([blob]);
var encoded = Utilities.base64Encode(compressedBlob.getDataAsString());
activeSheet.getRange(1, 1).setValue(encoded);
}
function readLargeJson()
{
var activeSheet = SpreadsheetApp.getActiveSheet();
var values = activeSheet.getSheetValues(1, 1, 1, 1);
var value = values[0, 0];
var decoded = Utilities.base64Decode(value);
var blob = Utilities.newBlob(decoded);
var unzipped = Utilities.unzip(blob);
var obj = JSON.parse(unzipped.getDataAsString());
Browser.msgBox('Test Json array size', "" + obj["dummy"].length, Browser.Buttons.OK);
}

我不一定需要使用blob接口来压缩json，任何压缩json字符串并可存储在单元格中以便稍后检索原始json的解决方案都可以工作。

这是一个有趣的概念，所以我搜索了一下，找到了一些工作。

在您的代码中，您必须使用compressedBlob.getBytes()来编码数据，而不是compressedBlob.getDataAsString((。要在读取函数中创建blob，您必须在输入中使用字节。

然后在您的read函数中，unzip返回一个数组，在获取数据之前，您必须使用getAs()函数。所以你必须有unzipped[0].getAs('application/octet-stream').getDataAsString()而不是解压的.getDataAsString((

我做了一个单一的功能来简化测试，所以你只需要根据需要进行拆分。

function insertReadLargeJson(){
var obj = {};
obj["dummy"] = [];

// Just to make the json huge
for (var i = 0; i < 10000; i++)
{
obj["dummy"].push("value");
}
//    Logger.log(obj)

var activeSheet = SpreadsheetApp.getActiveSheet();

var str = JSON.stringify(obj);

var blob = Utilities.newBlob(str, 'application/octet-stream');
var compressedBlob = Utilities.zip([blob]);

var encoded = Utilities.base64Encode(compressedBlob.getBytes());

activeSheet.getRange(1, 1).setValue(encoded);

var decoded = Utilities.base64Decode(encoded);

var blob = Utilities.newBlob(decoded,'application/zip');

var unzipped = Utilities.unzip(blob);

var obj = JSON.parse(unzipped[0].getAs('application/octet-stream').getDataAsString());
//    Logger.log(JSON.stringify(obj))
Logger.log(obj.dummy.length)
//    Logger.log('Test Json array size', "" + obj["dummy"].length)
}

我能够在外部库的帮助下完成这项工作。

我将代码转换为应用程序可读脚本：

JSONPACK.gs

var TOKEN_TRUE = -1;
var TOKEN_FALSE = -2;
var TOKEN_NULL = -3;
var TOKEN_EMPTY_STRING = -4;
var TOKEN_UNDEFINED = -5;
function pack(json, options) {

// Canonizes the options
options = options || {};

// A shorthand for debugging
var verbose = options.verbose || false;

verbose && console.log('Normalize the JSON Object');

// JSON as Javascript Object (Not string representation)
json = typeof json === 'string' ? this.JSON.parse(json) : json;

verbose && console.log('Creating a empty dictionary');

// The dictionary
var dictionary = {
strings : [],
integers : [],
floats : []
};

verbose && console.log('Creating the AST');

// The AST
var ast = (function recursiveAstBuilder(item) {

verbose && console.log('Calling recursiveAstBuilder with ' + this.JSON.stringify(item));

// The type of the item
var type = typeof item;

// Case 7: The item is null
if (item === null) {
return {
type : 'null',
index : TOKEN_NULL
};
}

//add undefined 
if (typeof item === 'undefined') {
return {
type : 'undefined',
index : TOKEN_UNDEFINED
};
}

// Case 1: The item is Array Object
if ( item instanceof Array) {

// Create a new sub-AST of type Array (@)
var ast = ['@'];

// Add each items
for (var i in item) {

if (!item.hasOwnProperty(i)) continue;

ast.push(recursiveAstBuilder(item[i]));
}

// And return
return ast;

}

// Case 2: The item is Object
if (type === 'object') {

// Create a new sub-AST of type Object ($)
var ast = ['$'];

// Add each items
for (var key in item) {

if (!item.hasOwnProperty(key))
continue;

ast.push(recursiveAstBuilder(key));
ast.push(recursiveAstBuilder(item[key]));
}

// And return
return ast;

}

// Case 3: The item empty string
if (item === '') {
return {
type : 'empty',
index : TOKEN_EMPTY_STRING
};
}

// Case 4: The item is String
if (type === 'string') {

// The index of that word in the dictionary
var index = indexOf.call(dictionary.strings, item);

// If not, add to the dictionary and actualize the index
if (index == -1) {
dictionary.strings.push(encode(item));
index = dictionary.strings.length - 1;
}

// Return the token
return {
type : 'strings',
index : index
};
}

// Case 5: The item is integer
if (type === 'number' && item % 1 === 0) {

// The index of that number in the dictionary
var index = indexOf.call(dictionary.integers, item);

// If not, add to the dictionary and actualize the index
if (index == -1) {
dictionary.integers.push(base10To36(item));
index = dictionary.integers.length - 1;
}

// Return the token
return {
type : 'integers',
index : index
};
}

// Case 6: The item is float
if (type === 'number') {
// The index of that number in the dictionary
var index = indexOf.call(dictionary.floats, item);

// If not, add to the dictionary and actualize the index
if (index == -1) {
// Float not use base 36
dictionary.floats.push(item);
index = dictionary.floats.length - 1;
}

// Return the token
return {
type : 'floats',
index : index
};
}

// Case 7: The item is boolean
if (type === 'boolean') {
return {
type : 'boolean',
index : item ? TOKEN_TRUE : TOKEN_FALSE
};
}

// Default
throw new Error('Unexpected argument of type ' + typeof (item));

})(json);

// A set of shorthands proxies for the length of the dictionaries
var stringLength = dictionary.strings.length;
var integerLength = dictionary.integers.length;
var floatLength = dictionary.floats.length;

verbose && console.log('Parsing the dictionary');

// Create a raw dictionary
var packed = dictionary.strings.join('|');
packed += '^' + dictionary.integers.join('|');
packed += '^' + dictionary.floats.join('|');

verbose && console.log('Parsing the structure');

// And add the structure
packed += '^' + (function recursiveParser(item) {

verbose && console.log('Calling a recursiveParser with ' + this.JSON.stringify(item));

// If the item is Array, then is a object of
// type [object Object] or [object Array]
if ( item instanceof Array) {

// The packed resulting
var packed = item.shift();

for (var i in item) {

if (!item.hasOwnProperty(i)) 
continue;

packed += recursiveParser(item[i]) + '|';
}

return (packed[packed.length - 1] === '|' ? packed.slice(0, -1) : packed) + ']';

}

// A shorthand proxies
var type = item.type, index = item.index;

if (type === 'strings') {
// Just return the base 36 of index
return base10To36(index);
}

if (type === 'integers') {
// Return a base 36 of index plus stringLength offset
return base10To36(stringLength + index);
}

if (type === 'floats') {
// Return a base 36 of index plus stringLength and integerLength offset
return base10To36(stringLength + integerLength + index);
}

if (type === 'boolean') {
return item.index;
}

if (type === 'null') {
return TOKEN_NULL;
}

if (type === 'undefined') {
return TOKEN_UNDEFINED;
}

if (type === 'empty') {
return TOKEN_EMPTY_STRING;
}

throw new TypeError('The item is alien!');

})(ast);

verbose && console.log('Ending parser');

// If debug, return a internal representation of dictionary and stuff
if (options.debug)
return {
dictionary : dictionary,
ast : ast,
packed : packed
};

return packed;

};
function unpack(packed, options) {

// Canonizes the options
options = options || {};

// A raw buffer
var rawBuffers = packed.split('^');

// Create a dictionary
options.verbose && console.log('Building dictionary');
var dictionary = [];

// Add the strings values
var buffer = rawBuffers[0];
if (buffer !== '') {
buffer = buffer.split('|');
options.verbose && console.log('Parse the strings dictionary');
for (var i=0, n=buffer.length; i<n; i++){
dictionary.push((buffer[i]));
}
}

// Add the integers values
buffer = rawBuffers[1];
if (buffer !== '') {
buffer = buffer.split('|');
options.verbose && console.log('Parse the integers dictionary');
for (var i=0, n=buffer.length; i<n; i++){
dictionary.push(base36To10(buffer[i]));
}
}

// Add the floats values
buffer = rawBuffers[2];
if (buffer !== '') {
buffer = buffer.split('|')
options.verbose && console.log('Parse the floats dictionary');
for (var i=0, n=buffer.length; i<n; i++){
dictionary.push(parseFloat(buffer[i]));
}
}
// Free memory
buffer = null;

options.verbose && console.log('Tokenizing the structure');

// Tokenizer the structure
var number36 = '';
var tokens = [];
var len=rawBuffers[3].length;
for (var i = 0; i < len; i++) {
var symbol = rawBuffers[3].charAt(i);
if (symbol === '|' || symbol === '$' || symbol === '@' || symbol === ']') {
if (number36) {
tokens.push(base36To10(number36));
number36 = '';
}
symbol !== '|' && tokens.push(symbol);
} else {
number36 += symbol;
}
}

// A shorthand proxy for tokens.length
var tokensLength = tokens.length;

// The index of the next token to read
var tokensIndex = 0;

options.verbose && console.log('Starting recursive parser');

return (function recursiveUnpackerParser() {

// Maybe '$' (object) or '@' (array)
var type = tokens[tokensIndex++];

options.verbose && console.log('Reading collection type ' + (type === '$' ? 'object' : 'Array'));

// Parse an array
if (type === '@') {

var node = [];

for (; tokensIndex < tokensLength; tokensIndex++) {
var value = tokens[tokensIndex];
options.verbose && console.log('Read ' + value + ' symbol');
if (value === ']')
return node;
if (value === '@' || value === '$') {
node.push(recursiveUnpackerParser());
} else {
switch(value) {
case TOKEN_TRUE:
node.push(true);
break;
case TOKEN_FALSE:
node.push(false);
break;
case TOKEN_NULL:
node.push(null);
break;
case TOKEN_UNDEFINED:
node.push(undefined);
break;
case TOKEN_EMPTY_STRING:
node.push('');
break;
default:
node.push(dictionary[value]);
}

}
}

options.verbose && console.log('Parsed ' + this.JSON.stringify(node));

return node;

}

// Parse a object
if (type === '$') {
var node = {};

for (; tokensIndex < tokensLength; tokensIndex++) {

var key = tokens[tokensIndex];

if (key === ']')
return node;

if (key === TOKEN_EMPTY_STRING)
key = '';
else
key = dictionary[key];

var value = tokens[++tokensIndex];

if (value === '@' || value === '$') {
node[key] = recursiveUnpackerParser();
} else {
switch(value) {
case TOKEN_TRUE:
node[key] = true;
break;
case TOKEN_FALSE:
node[key] = false;
break;
case TOKEN_NULL:
node[key] = null;
break;
case TOKEN_UNDEFINED:
node[key] = undefined;
break;
case TOKEN_EMPTY_STRING:
node[key] = '';
break;
default:
node[key] = dictionary[value];
}

}
}

options.verbose && console.log('Parsed ' + this.JSON.stringify(node));

return node;
}

throw new TypeError('Bad token ' + type + ' isn't a type');

})();

}
function indexOfDictionary (dictionary, value) {

// The type of the value
var type = typeof value;

// If is boolean, return a boolean token
if (type === 'boolean')
return value ? TOKEN_TRUE : TOKEN_FALSE;

// If is null, return a... yes! the null token
if (value === null)
return TOKEN_NULL;

//add undefined
if (typeof value === 'undefined')
return TOKEN_UNDEFINED;


if (value === '') {
return TOKEN_EMPTY_STRING;
}

if (type === 'string') {
value = encode(value);
var index = indexOf.call(dictionary.strings, value);
if (index === -1) {
dictionary.strings.push(value);
index = dictionary.strings.length - 1;
}
}

// If has an invalid JSON type (example a function)
if (type !== 'string' && type !== 'number') {
throw new Error('The type is not a JSON type');
};

if (type === 'string') {// string
value = encode(value);
} else if (value % 1 === 0) {// integer
value = base10To36(value);
} else {// float

}

// If is number, "serialize" the value
value = type === 'number' ? base10To36(value) : encode(value);

// Retrieve the index of that value in the dictionary
var index = indexOf.call(dictionary[type], value);

// If that value is not in the dictionary
if (index === -1) {
// Push the value
dictionary[type].push(value);
// And return their index
index = dictionary[type].length - 1;
}

// If the type is a number, then add the '+'  prefix character
// to differentiate that they is a number index. If not, then
// just return a 36-based representation of the index
return type === 'number' ? '+' + index : index;

};
function encode(str) {
if ( typeof str !== 'string')
return str;

return str.replace(/[+ |^%]/g, function(a) {
return ({
' ' : '+',
'+' : '%2B',
'|' : '%7C',
'^' : '%5E',
'%' : '%25'
})[a]
});
};
function decode(str) {
if ( typeof str !== 'string')
return str;

return str.replace(/+|%2B|%7C|%5E|%25/g, function(a) {
return ({
'+' : ' ',
'%2B' : '+',
'%7C' : '|',
'%5E' : '^',
'%25' : '%'
})[a]
})
};
function base10To36(number) {
return Number.prototype.toString.call(number, 36).toUpperCase();
};
function base36To10(number) {
return parseInt(number, 36);
};
function indexOf(obj, start) {
for (var i = (start || 0), j = this.length; i < j; i++) {
if (this[i] === obj) {
return i;
}
}
return -1;
};

有了这个，你的脚本现在可以是：

代码.gs

function insertLargeJson()
{
var obj = {};
obj["dummy"] = [];
// Just to make the json huge
for (var i = 0; i < 10000; i++)
{
obj["dummy"].push("value");
}
var activeSheet = SpreadsheetApp.getActiveSheet();

var packed = pack(obj);

var value = JSON.stringify(packed);

activeSheet.getRange(1, 1).setValue(value);
}
function readLargeJson()
{
var activeSheet = SpreadsheetApp.getActiveSheet();

var value = activeSheet.getRange(1,1).getValue();

var packed = JSON.parse(value);

var obj = unpack(packed);

Browser.msgBox('Test Json array size', "" + obj["dummy"].length, Browser.Buttons.OK);
}

希望这能有所帮助！

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium