Skip to content

Instantly share code, notes, and snippets.

@sokol815
Last active October 13, 2022 02:35
Show Gist options
  • Save sokol815/139ebf969d32100428c4cd4161204615 to your computer and use it in GitHub Desktop.
Save sokol815/139ebf969d32100428c4cd4161204615 to your computer and use it in GitHub Desktop.
QA GSheets API

I've got a youtube video demonstrating the setup process and explaining usage: https://www.youtube.com/watch?v=SSm3Sw3EsQ8. This basically goes over the same content we're going to see below.

Instructions for use:

  1. Create a new Google Spreadsheet.
  2. Open the spreadsheet and name it "QuickAlerts Spreadsheet".
  3. Rename "Sheet 1" call it AlertExport
  4. Go to Extensions -> Apps Script.
  5. In the new tab that popped up, Change the project title from "Untitled project" to "QA GSheets API".
  6. Replace the code in your new project's code.gs with the code from this gist's code.gs.
  7. Save Project.
  8. Go to Deploy Top right of screen) -> new > select Type Gear Wheel > web App > enter in Title "QA GSHEETS 1.0" > Deploy
  9. Change "Who has access to the app" to "Only myself"
  10. On the "Authorization required" dialog, select "Review Permissions"
  11. Choose the account that will be using chrome while you use QA.
  12. An "this app isn't verified" popup will occur, click "Advanced" -> "Go to QA Gsheets (unsafe)".
  13. On the "QA GSheets API wants to access your Google Account" page, scroll down and select "Allow"
  14. in the "Deploy as web app" popup that appears, copy the "Current web app URL" and put this in the QA app drawer for "GSheets URL"
  15. You're ready to use Google Sheets with QA.

As to why this is considered "unsafe" it's because Google is notifying you they have not approved, nor reviewed the contents of this script. It will have full control over the spreadsheet it is attached to, be that for good or bad. All the code is readily visible above here, nothing heinous is being done. I'm looking into publishing this as an "API Executable" in the future.

If you ever want to remove this functionality, go to the project, and select "Delete Project" in the dropdown.

If you have any questions, contact me.

// 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());
}
@faisal305motairi
Copy link

i want this

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment