0

How could one go about obtaining a reference to a newly created or imported sheet?

The method I tried was getting an array of all sheets in the active spreadsheet and iterating through those sheet objects to get the sheet with the highest ID number. I did this because the documentation for Sheet.getSheetId() notes that:

The ID is a monotonically increasing integer assigned at sheet creation time that is independent of sheet position.

and I took that to mean that the ID of every new sheet is always higher than the ID of any other existing sheet. But I quickly found out with a bit of experimenting that new sheets are not guaranteed to have the highest ID. In particular, I found that if you create a new sheet and rename it, then that new sheet does not always have the highest ID, but if you leave the name as is (i.e. "Sheet5", etc.), then the new sheet does have the highest ID.

I'm new to Google Apps scripting so I admit that this whole sheet ID business is confusing me. All I want is to be able to get a reference to the most newly created or imported sheet.

Manuel
  • 2,143
  • 5
  • 20
  • 22

1 Answers1

0

Here is an untested idea (and a bit of a workaround):

You could create a menu item that allows you to create sheets programmatically using Google Apps scripts, and include a timestamp int the sheet name as you do it. Here is a quick code example from SO on creating sheets using Google Apps scripts:

var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var yourNewSheet = activeSpreadsheet.getSheetByName("Name of your new sheet");

if (yourNewSheet != null) {
    activeSpreadsheet.deleteSheet(yourNewSheet);
}

yourNewSheet = activeSpreadsheet.insertSheet();
yourNewSheet.setName("Name of your new sheet");

Once you've done that, you could use the getSheets() method to look at all sheets for a particular spreadsheet and evaluate the time stamps.

The downside is that the normal way of creating sheets along the bottom of the page cannot be used, you'd have to to access it form your custom menu.

Community
  • 1
  • 1
sco_tt
  • 303
  • 1
  • 8
  • Thank you. While this is useful for sheets that are created, I also need a way to reference sheets that are imported. – Manuel Jan 28 '17 at 04:02