Google Sheets - Get value in same cell in previous sheet

Question

I'm creating a workout spreadsheet to track my performance week-to-week, with each sheet covering a week's worth of workouts. Each sheet (aside from the first) is a duplicate of the sheet before it, and there are some values I'd like to carry over from the previous sheet; for instance, the number of reps for an exercise from the previous week.

I've read through some of the API but can't seem to find a function that selects the current sheet where the function is called from. If it existed, I could get the index and subtract from it to get the previous sheet, then grab a value from a given row and column. However, the closest thing I can find is accessing the "Active" sheet - something that seems to be set using the setActiveSheet() function. But I don't see how I can use this in a single script meant to be used in multiple different cells.

Am I missing something here, or is there a better way to grab information about the currently-selected sheet? Any help you can provide would be very helpful. Thank you!

[EDIT] My spreadsheet is located here: https://docs.google.com/spreadsheets/d/1b8rDYxbb_6ltI0GvKcmKyz1Y3IV3BSrgWp8Mf2l18cc/edit?usp=sharing

Additionally, I will post the script below.

Answer 1

to get the current sheet you can use .getActiveSheet() and then use .getIndex(). Then substract from there. Note that .getIndex() is 1-indexed. So the first sheet will have index 1. If you want to use that in combination with .getSheets() (which is zero-indexed) you will have to do -2 to get to the previous sheet. If needed you may have to build in some error handling (in case you are on the first sheet already). E.g:

function previous() {
var ss = SpreadsheetApp.getActive();
var curSheet = ss.getActiveSheet();
var prevSheet = ss.getSheets()[curSheet.getIndex() - 2];
prevSheet ? Logger.log(prevSheet.getName()) : SpreadsheetApp.getUi()
    .alert("Active Sheet is the first sheet !");
}

Answer 2

I don't know if you still need them, but I made this script (my dad needed it)

function previousCell(cellLocation) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var curSheet = ss.getActiveSheet();
  var curSheetIndex = curSheet.getIndex();
  var preSheetIndex = curSheetIndex - 2;
  var preSheet = ss.getSheets()[preSheetIndex];
  var targetCell = preSheet.getRange(cellLocation);
  var value = targetCell.getValue();
  if (curSheetIndex == 1){
  return "There is no previous sheet" ;
  }

  else    
  return value;
}

I have submitted it as Sheets add-on just now, let's see if it will appear soon

Answer 3

In case this is still of interest, here is my (generic solution):

/**
 * Get name of a given sheet, based on index
 * @param {number}  index          
 *      If positive: See relativeOffset; If negative: A previous sheet (-1 means the one preceeding), otherwise (default) : The current sheet
 * @param {boolean} relativeOffset
 *      If TRUE, a positive index means relative to current sheet tab, otherwise (FALSE=default) absolute sheet index (first is 1)
 * @return The name of the relevant sheet
 * @customfunction
 */
function getSheetName(index,relativeOffset) {
  var ss = SpreadsheetApp.getActive();
  var curSheet = ss.getActiveSheet();
  var curSheetIndex = curSheet.getIndex();
  var getSheetIndex = curSheetIndex - 1;
  
  if (index > 0 && !relativeOffset) {
    getSheetIndex = index -1;
  } else if (index > 0) {
    getSheetIndex = getSheetIndex + index;
  } else if (index < 0) {
    getSheetIndex = curSheetIndex + index;
  }
  
  if (getSheetIndex < 0){
    return false;
  }
  
  var getSheet = ss.getSheets()[getSheetIndex];
  //For debugging: 
  //return ("curSheetIndex : " + curSheetIndex + " ; index : "+ index + " ; getSheetIndex : " + getSheetIndex + " ; " + getSheet.getName());
  return getSheet.getName();  
}

Answer 4

William's answer works. However when pass the cell location as String for the function parameter for example previousCell("B$1"), when drag and copy the cell, the B$1 will keep the same. To solve this issue, I created another function with relative column location, so it will always access the same row and relative columns from the previous sheet:

function previousCell(relCol) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var curSheet = ss.getActiveSheet();
  if (curSheet < 2)
    return "There is no previous sheet";
  var preSheet = ss.getSheets()[curSheet.getIndex() - 2];
  var curCel = curSheet.getActiveCell();
  return preSheet.getRange(curCel.getRow(), curCel.getColumn() + relCol).getValue();
}

So it will call the function as: previousCell(2), it will access the cell with same row and 2 columns right from previous sheet.