Wednesday, July 25, 2012

Inter-Spreadsheet Collaboration

Google spreadsheets are great for collaborative work since spreadsheets can be shared and edited by groups of users.  The following code examples show how JavaScript can be used to open and edit spreadsheets other than the one in which the actual code resides.  Furthermore, there is an example of how the details of those accessing shared spreadsheets can be logged.  In the code below a spreadsheet owned by one user account is opened and edited by another user, i.e. the one executing the JavaScript.  Although the examples given are simplistic, they do suggest how powerful this type of approach can be.  A user can create a spreadsheet with attached JavaScript code, grant appropriate privileges to other Google accounts so that the code can interact with another spreadsheet to manipulate and edit it.

Here are the code examples:


// This is the calling function
function call_openAnotherSpreadsheet() {
  var spreadsheetID = 'google spreadsheet ID';

  try {
    var spreadsheetFile = openAnotherSpreadsheet(spreadsheetID);
  } catch(error) {
    Logger.log('Unable to open given spreadsheet, check error log');
    return;
  }

  try {
    SpreadsheetApp.setActiveSpreadsheet(spreadsheetFile);
    logOpen(spreadsheetFile, 1);
    Logger.log(SpreadsheetApp.getActive().getName());
    Logger.log(SpreadsheetApp.getActive().getNumSheets());
    Logger.log(SpreadsheetApp.getActive().getDataRange().getA1Notation());
    Logger.log(SpreadsheetApp.getActive().getDataRange().getValues());
  } catch(error) {
    Logger.log('ERROR:' + error);
  }
}


function openAnotherSpreadsheet(spreadsheetID) {
  try {
    return SpreadsheetApp.openById(spreadsheetID);
  } catch(error) {
    Logger.log('Error thrown in "openAnotherSpreadsheet" opening given spreadsheet');
    throw(error);
  }
}


function logOpen(spreadsheet, sheetIndex) {
  var targetSheet = spreadsheet.getSheets()[sheetIndex];
  var timestamp = new Date();
  Logger.log(targetSheet.getName());
  var nextRow = targetSheet.getDataRange().getNumRows() + 1;
  var targetCellAddress = 'A' + nextRow;
  targetSheet.getRange(targetCellAddress).setValue(Session.getEffectiveUser().getEmail());
  targetSheet.getRange(targetCellAddress).offset(0,1).setValue(timestamp);
}

To execute the above, paste the code into the script editor, provide an authentic spreadsheet ID as displayed in the spreadsheet URL, this will be the long alphanumeric string between "https://docs.google.com/spreadsheet/ccc?key=" and the trailing "#gid=0".  This ID can be either the spreadsheet containing the code, another spreadsheet, yours or one for which you've been granted edit permissions.  After executing the code, the script should have logged some information into the Logger, to view, select View->Logs from the script editor and also check the target spreadsheet, the one for which you provided the Google ID.  The log should contains the information written by the code in the second try .. catch block in the the function "call_openAnotherSpreadsheet()" while some details on who executed the script (the owner or someone who is sharing the spreadsheet containing the scripts) are recorded by the call  to "logOpen()".


Some Code Notes

  • JavaScript offers structured exception handling via the Java-inspired try-catch-finally mechanism.  I'll return to this in a later post but such actions as opening another spreadsheet are well suited to being placed in a try block where if an exception is thrown by such an action, the error can be logged and then re-thrown.
  • I've used the Logger.log method for the first time to record output.  This provides a convenient and less intrusive method of recording actions and output than the Browser.msgBox mechanism used in earlier posts.
  • Since, I've been away from JavaScript and Google spreadsheet apps for a while, I've re-visited some methods of the SpreadsheetApp and Range classes in the "call_openAnotherSpreadsheet()".  See the calls to "get" methods of the active spreadsheet class in this function and cross-check these with what has been written to the Logger.log, this should clarify what these method calls are doing.
  • The "logOpen()" function does a few interesting things.  Given a spreadsheet instance and a sheet index, it retrieves an instance of the sheet with that instance, determines the number of used rows and increments it by 1(.getDataRange().getNumRows() + 1) .  It then creates an address for column A (var targetCellAddress = 'A' + nextRow;) and uses this address to record the effective user's (whoever is executing the script) email in column A and a date stamp in column B.  Next time the script is called, it re-uses this logic to determine the next available row.
  • Leaving aside the code details, far and away the most important point in the above examples is the facility with which users can use Google spreadsheets and JavaScript to collaborate!  Scripts can be written by anyone and shared with anyone who has a Google account and these scripts can be used to manipulate any spreadsheets for which the script user has permissions.  All very powerful.


No comments:

Post a Comment