|
// Usage |
|
// code and directions from: https://gist.github.com/sokol815/139ebf969d32100428c4cd4161204615 |
|
// created by @sokol#6328 on discord. |
|
|
|
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service |
|
|
|
//function doGet(e){ |
|
// return handleResponse(e); |
|
//} |
|
|
|
function doPost(e){ |
|
return handleResponse(e); |
|
} |
|
|
|
function handleResponse(e) { |
|
var lock = LockService.getDocumentLock(); |
|
lock.waitLock(30000); // wait 30 seconds before conceding defeat. |
|
|
|
if( SCRIPT_PROP.getProperty("key") == null ) { |
|
_setup(); |
|
} |
|
|
|
try { |
|
|
|
var content = JSON.parse(e.postData.contents); |
|
if( content.action !== undefined ) { |
|
switch( content.action ) { |
|
case 'createSheet': |
|
return createSheet( content.sheet, content.headers ); |
|
case 'deleteSheet': |
|
return deleteSheet(content.sheet); |
|
case 'insertRows': |
|
return insertRows( content.sheet, content.headers, content.rows ); |
|
case 'getSheetNames': |
|
return getSheetNames(); |
|
case 'getSheetContents': |
|
return getSheetContents( content.sheet ); |
|
default: |
|
return _response(false,"Unknown named action \""+content.action+"\".",[]); |
|
} |
|
} |
|
|
|
} catch(e){ |
|
|
|
// if error return this |
|
return ContentService |
|
.createTextOutput(JSON.stringify({"success":false, "message":"An error was encountered", "error": e})) |
|
.setMimeType(ContentService.MimeType.JSON); |
|
|
|
} finally { //release lock |
|
|
|
lock.releaseLock(); |
|
|
|
} |
|
} |
|
|
|
function createSheet(sheetName, headers ) { |
|
// next set where we write the data - you could write to multiple/alternate destinations |
|
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); |
|
var sheet = doc.getSheetByName(sheetName); |
|
if( sheet !== null ) { |
|
sheet.clear(); |
|
} else { |
|
doc.insertSheet(sheetName); |
|
sheet = doc.getSheetByName(sheetName); |
|
} |
|
|
|
headers = _regularizeRegion(headers); |
|
|
|
sheet.getRange(1,1,headers.length, headers[0].length).setValues(headers); |
|
return _response(true,"sheet created",[]); |
|
} |
|
|
|
function deleteSheet(sheetName){ |
|
// next set where we write the data - you could write to multiple/alternate destinations |
|
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); |
|
var sheet = doc.getSheetByName(sheetName); |
|
|
|
if( sheet == null ) { |
|
return _response(false,'No sheet by name of "'+sheetName+'" in document.',[]); |
|
} |
|
|
|
doc.deleteSheet(sheet); |
|
return _response(true,"Sheet \""+sheetName+"\" deleted successfully.", []); |
|
} |
|
|
|
function insertRows(sheetName, headers, content) { |
|
|
|
if( !Array.isArray(content) || !Array.isArray(content[0])) { |
|
return _response(false,"Must pass an array of arrays e.g. [['a','b',c']] to insert into sheet", [] ); |
|
} |
|
|
|
content = _regularizeRegion(content); |
|
|
|
sheet = _getSheet( sheetName ); |
|
if( sheet == null ) { |
|
return _response(false, "could not get sheet", [] ); |
|
} |
|
|
|
var nextRow = sheet.getLastRow()+1; // get next row |
|
|
|
if( nextRow == 1 ) { // --- if empty, insert headers |
|
headers = _regularizeRegion(headers); |
|
sheet.getRange(nextRow, 1, headers.length, headers[0].length).setValues(headers); |
|
nextRow = sheet.getLastRow()+1; // get next row |
|
} |
|
|
|
// more efficient to set values as [][] array than individually |
|
sheet.getRange(nextRow, 1, content.length, content[0].length).setValues(content); |
|
// return json success results |
|
return _response( true, content.length + " rows inserted.", [] ); |
|
} |
|
|
|
function getSheetNames() { |
|
var names = []; |
|
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); |
|
var sheets = doc.getSheets(); |
|
for(var i = 0; i < sheets.length; i++ ) { |
|
names.push(sheets[i].getName()); |
|
} |
|
return _response(true,"",{names: names}); |
|
} |
|
|
|
function getSheetContents( sheetName ) { |
|
sheet = _getSheet( sheetName ); |
|
if( sheet == null ) { |
|
return _response(false, 'Sheet "'+sheetName+'" does not exist.',[]); |
|
} |
|
var values = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues(); |
|
return _response(true,"",{sheet: sheetName, values: values}); |
|
} |
|
|
|
function _regularizeRegion(data){ |
|
var output = []; |
|
var maxLength = data.map(function(d){ return d.length; }).reduce(function(acc, cur){ return Math.max(acc,cur); }); |
|
for( var i = 0; i < data.length; i++ ) { |
|
var cur = []; |
|
for( var j = 0; j < maxLength;j++) { |
|
if( j >= data[i].length ) { |
|
cur.push(''); |
|
} else { |
|
cur.push(data[i][j]); |
|
} |
|
} |
|
output.push(cur); |
|
} |
|
return output; |
|
} |
|
|
|
function _getSheet( sheetName ) { |
|
// next set where we write the data - you could write to multiple/alternate destinations |
|
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); |
|
var sheet = doc.getSheetByName(sheetName); |
|
if( sheet == null ) { |
|
_response(false,'No sheet by name of "'+sheetName+'" in document.',[]); |
|
} |
|
return sheet; |
|
} |
|
|
|
function _response( success, message, data ) { |
|
if( success == undefined ) { success = true; } |
|
if( message == undefined ) { message = ""; } |
|
if( data == undefined ) { data = []; } |
|
return ContentService |
|
.createTextOutput(JSON.stringify({"success":success, "message": message, "data": data })) |
|
.setMimeType(ContentService.MimeType.JSON); |
|
} |
|
|
|
function _setup() { |
|
var doc = SpreadsheetApp.getActiveSpreadsheet(); |
|
SCRIPT_PROP.setProperty("key", doc.getId()); |
|
} |
i want this