I have an app with about 80 sheets/tables (is there an option in google sheets that give you ‘sheet count’) and about 20 Glide tables.
I just created a duplicate Project and selected “Copy the sheet” which created a “Copy of” the google data. I am not sure if it copied the Glide tables thought.
Before I start deleting most the data to test the APP from ‘scratch’ I wanted to understand if their are any nuances (like Glide tables) that I have to be concerned with. I am ‘archiving’ the current version of the APP and Data (but again, not sure if/how to archive Glide tables).
All insights appreciated, I am not a developer (but I did stay at a Holiday Inn Express for the last 3 months )
Not that I am aware of, but I have a bit of Apps Script that I run daily which takes a “stocktake” of all the sheets in my Google Spreadsheet. You’re welcome to use it if you want.
To use it, you just need to create a sheet and name it “Status”, then add 3 columns titled “Sheet”, “Rows” & “Columns”.
The script will count the number of rows & columns in every sheet, and write the results to this table.
You can either run it manually, or run it on a regular basis using a trigger.
function count_rows_and_columns() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet_data = {};
sheets.forEach(function (sheet) {
sheet_data[sheet.getName()] = [sheet.getLastRow(), sheet.getLastColumn()];
});
var sheet = ss.getSheetByName('Status');
var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();
var sheet_index = headers.indexOf('Sheet');
var rows_index = headers.indexOf('Rows');
var cols_index = headers.indexOf('Columns');
var row = 2;
var data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
while (data.length>0) {
var this_row = data.shift();
var sheet_name = this_row[sheet_index];
if (sheet_data.hasOwnProperty(sheet_name)) {
sheet.getRange(row,rows_index+1).setValue(sheet_data[sheet_name][0]);
sheet.getRange(row,cols_index+1).setValue(sheet_data[sheet_name][1]);
delete sheet_data[sheet_name];
}
row++;
}
if (Object.keys(sheet_data).length > 0) {
Object.keys(sheet_data).forEach(function (sheet_name) {
sheet.getRange(row,sheet_index+1).setValue(sheet_name);
sheet.getRange(row,rows_index+1).setValue(sheet_data[sheet_name][0]);
sheet.getRange(row,cols_index+1).setValue(sheet_data[sheet_name][1]);
row++;
});
}
}
Open the script editor (Extensions->Apps Script), paste in there, save, and run.
If you’ve never run any apps script in that spreadsheet before, which sounds like the case, you’ll be asked to give permission the first time you run it.
No results written. This is what the Status.gs looks like; I created the Status.gs file and pasted in the code then hit run (after giving it permission). Finished really fast but no data written to the “Status” sheet (see below).
Ah, you need to add one row to get it started.
If you just add the name of any sheet in row 2, that should do it.
If that doesn’t work, let me know and I’ll take a look tomorrow (late here now).
@MattLB sorry it was quite late last night when I posted this. Anyway, I just double-checked and the script does need one row apart from the header row to kick it off. So from your screen shot, if you just add “Home” in cell A2, then it will work. An extra row will be added automatically for each sheet that it finds.
I tried to get rid of myFunction() earlier and it said “Could not find myFunction()”.
So I did it again just now (clicked on RUN) - same results - “Could not find myFunction()”.
Ran it in debug mode - WORKED.
Ran it again from RUN - WORKED.
yeah, that’s because it would have still been trying to run myFunction, which no longer exists.
You could have just selected the correct function from the dropdown at the top, next to the Debug button.
Over half are templates for filtering by type (3), dates (4) and team (1) and channel partner (1) and hierarchy (5).
Need to be able to run reports for any given quarter at any given time and compare quarter over quarter actuals by type/partner and with the correct view from the hierarchy (Manager, Director, Sr Director, VP, CxO)
Filtering relations would have been a LOT easier.
SO I HAVE A WHOLE LOTTA TEMPLATES
BTW…I wish I could copy templates (computed columns) from one sheet to another because my life would have been so much easier.
Good point. Looking at the data I have about 40 ‘objects’ with 20-ish columns per. I keep lots of dates and hierarchy info for each item. Will look more closely once I move this out of alpha and do more clean-up!
