QA GSheets API

readme.md

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.

• on discord: @sokol#6328
• email: support@quickalerts.us

code.gs

// 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