Sun, 18 Dec 2016

Reference the Previous Sheet in Google Spreadsheets

I'm maintaining this soccer stats sheet and have different games on different sheets. To build up sums I like to reference data from the previous sheet. It took me some trial and error to get going. Essentially it requires a custom function and the use of INDIRECT.

For the custom function go to Tools > Script Editor and paste the following code

/**
 * Retrieves a reference to the previous sheet, or null
 * input is unused.
 */
function prevSheet(input) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = sheet.getSheets();
  var prev = null;
  for(var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == sheet.getSheetId()) {
      return prev.getName();
    }
    prev = sheets[i];
  }
  return null;
};

The code is also available here as an add-on.

To use the function you need to use the INDIRECT function, for example to reference cell A2 on the previous sheet:

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!A2")

To be able to copy the formula around and keeping the references, use

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!" & CHAR(64+COLUMN()) & ROW()

This was very helpful to get me started.

posted at 03:23 | path: /web | permanent link to this entry

Tue, 24 Feb 2015

A new blog entry in Markdown format

I took the markdown plugin from here and fixed a few issues, most notably making it an entry parser, losely based on the rst.py plugin.

This is awesome, because now I can use github to actually write blog entries in the browser - maybe, just maybe making the hurdle a bit less to write a few posts.

The resulting code can be found in my new github repository.

And yes, I know, version 1.5 will bring this by default - but I am on Debian Wheezy which runs 1.4.

posted at 23:42 | path: /web | permanent link to this entry

Fri, 20 Aug 2010

Javascript based Picasa photo roll on your webpage

From the department of 5-minute Javascript hacks comes this little gem, which handles the 'Random photo' to the right. It uses the JSON-API Google API to get the data from the Picasa-Web-Albums.

<script><!-- //http://code.google.com/apis/gdata/docs/json.html
// Creative Commons Attribution-ShareAlike 3.0 Unported License
// http://creativecommons.org/licenses/by-sa/3.0/
// (c) Jan Dittmer <jdi@l4x.org> 2010
var es = [];
function r() {
       var idx = Math.floor(Math.random()*es.length);
       var m = es[idx]['media\$group'];
       var i = document.getElementById("i");
       document.getElementById("t").innerHTML = m['media\$description']['\$t'];
       i.src = m['media\$thumbnail'][0]['url'];
       setTimeout("r();",10*1000);
}
function j(p) {
       es = p['feed']['entry'];
       r();
}
function picasa() {
       var url='http://picasaweb.google.com/data/feed/base/user/jan.dittmer' +
               '?kind=photo&thumbsize=160c&access=public&alt=json&callback=j';
       var s = document.createElement('script');
       s.src = url; document.body.appendChild(s);
}
window.onload = picasa;
//--></script>
<a href="http://picasaweb.google.com/jan.dittmer"> <img id="i"> </a>
<div id="t"></div>

posted at 20:56 | path: /web | permanent link to this entry

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.