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