Our company has set up a tracker for closed cases. We have two sections we're tracking: Collaborative Sessions and Quick Close.
We have a shared Google sheet where each team member has a tab where they enter the information and then there's a Team tab that has all of them combined. Rather than entering the information twice, I want to script it to populate the Team tab.
In columns A-E in Team we have: A: Blank B: Session Date (Date) C: Case # Reviewed (Number) D: Case Closed Date (Date) E: Closed as a result? (yes/no)
Columns G through L: G: Case Number (number) H: Case Submit Date (date) I: Case Grab Date (date) J: Date Closed (date) K: Number of days to close (number) L: Notes
The first one will be populated in row 12.
In the separate tabs for each person, the columns are the same.
This is what I've got so far:
function populateTeam() {
var ssh = SpreadsheetApp.getActiveSpreadsheet();
var val = ssh.getRange('Paula!A4:E999').getValues();
var sh_base = ssh.getSheetByName('Team');
var base = sh_base.getDataRange().getValues();
var row = base.length + 1;
var key = val[0][1].getValues();
for(var i = 0; i < base.length; i++){
if(base[i][0].getValues() == key) {
row = ++i;
break;
}
}
}
This is based on another script I wrote to pull data from one sheet and populate another.
Problem is that it throws an error on line 7: it says "TypeError: Cannot find function getValues in object . "
How can I fix this? I figure that once I can get this to work, the rest of it will be pretty straightforward - just changing the columns and the sheet names.
Thanks in advance for any help!
This should be possible using a formula such as:
=QUERY({Sheet1!A11:L;Sheet2!A12:L;Sheet3!A12:L},"where Col2 <>'' or Col2 is not null")
See this example sheet to see it working: https://docs.google.com/spreadsheets/d/1nsugHjCHJueYK5t_cIJR7co4rLAJKEWZc-5sQvQRjI4/edit?usp=sharing
You can add more sheet names and ranges into the { } array and separate them with ;