How to link to previous sheet in Google Sheets?

Question

Google Apps Script

Good afternoon. Tell me, please, how can I refer to the previous sheet (not manually select it, but automatically)?

When creating a new sheet, I want it to take data from the previous sheet (from the same cells).

Answer 1

Get the index of the current sheet:

const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheet = ss.getActiveSheet();
const index = sheet.getIndex();

Then get the previous sheet by index Do you need a formula?
ss. getSheets()[index - 2];

function getListPrev(range) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const index = sheet.getIndex();
  
  const formula = sheet.getActiveRange().getFormula();
  range = formula.match(/getListPrev\(([^\)]*?)\)/i)[1].trim();
  
  const sheet_prev = (ss.getSheets()[index - 2] || null);
  
  if (!sheet_prev)
    throw new Error('Нет предыдущего листа!');
  
  return sheet_prev
  .getRange(range)
  .getValues();
}

---
Maxim Stoyanov (stomaks), developer of Google Apps Script .
g-apps-script.com
stomaks.me

Answer 2

Write the name of the previous sheet in cell A1, refer to it in the formulas.