Search code examples
google-sheetsspreadsheet

How to create a table that adds a row for every sheet created?

i'm trying to help my dad (more or less 0 understanding of every aspect of IT) to check his expenses. So far I created everything he needs, based on facts that every year he has to create a new sheet to track his data (E.G. "22/23","23/24","24/25"...) when he needs to. What i find hard to achieve is , assuming i have a sheet called "total" which has to gather data from every sheet and do some calculations, find a way to create a table which has a "trigger" that adds a row everytime he adds a new sheet and gather data from it.

Is it possible to do this?

EDIT: is difficult to post an example sheet because what i'm trying to achieve is all dynamic. I try to better phrase the scenario.

I Have 2 sheets, "Calculations" and "22/23". Let's say 22/23.A1 has the value "test"

In Calculations A1 i'd want to have "22/23" and in A2 "test"

BUT

if my father adds a new sheet called "23/24" with A1:"Test1"

i'd also want :

    A      B
1  22/23   test
2  23/24   test1

And so on

Solution

  • RECOMMENDED SOLUTION

    Try using the Installable Triggers that Google Apps Script offers to automate what you'd like to do.

    To access the script editor from the Google Sheet, click on Extensions > Apps Script from the toolbar.

    image

    To add the installable trigger, click on Triggers > + Add Trigger on the left of the Google Apps Script.

    image

    You may use this sample script as a reference for what you'd like to accomplish:

    I have also added some explanations of what the code does

    const myFunction = () => {
  // Gets your current active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Gets all the sheets in the spreadsheet
  var sheets = ss.getSheets();
  // Gets the sheet named "Calculations"
  var calculationsSheet = ss.getSheetByName("Calculations");
  // Initialization of a new array
  var checkAllSheets = new Array();
  // Goes through all the sheets. Gets their name as well as the value of their "A1" cells
  for (let i = 1; i < sheets.length; i++) {
    checkAllSheets.push([sheets[i].getName(), sheets[i].getRange('A1').getValue()]);
  }
  // Gets the range of the "Calculations" sheet and pastes the values gathered on it's "A1" cell
  calculationsSheet.getRange(1, 1, checkAllSheets.length, checkAllSheets[0].length).setValues(checkAllSheets);
}

    I recommend running the code at least once, just so that the authorizations will be allowed.

    OUTPUT

    image

    REFERENCES