0

I am hoping that someone might be able to help me out, I've been doing a lot of searching in the past few days and coming up short of a solution. Likely has to do with my lack of knowledge on coding... I know enough to be dangerous, just not enough to be effective or efficient!

To start, here is my goal.

  • To create a document, that will contain a 'master list' of tasks for my marketing team.
  • Each team member (currently 4) will have their own tab, which would only display tasks assigned to their name & have blank spaces under both 'completed' and 'canceled' columns...
  • In addition to the team member tabs, there would be two others, 'Completed' and 'Canceled'. These tabs would contain any tasks, by any team member, that have a date under the respective column.

Currently, I managed to create a script that will delete all tabs (other than the master list) and recreate them with the assigned name. What I haven't been able to do is get each sheet to filter results. I can't seem to get any code that I've found in the forums to work. Nothing will filter the results automatically on any tab, it only copies the master list and renames the tabs. Can someone point me in the right direction?

Am I going about this in the most efficient way possible? Do I need a button that will run the script on command? Is there a way for the 'team member' sheets to update live based on what is entered into the master list? I tried to utilize this information (Filter data by column K in Google Script Editor (Google Sheets)) but was not able to make it function within my document.

Sorry for the lengthy, post, thanks in advance for any and all help!

A copy of the original file: https://docs.google.com/spreadsheets/d/141JochDYmt9RHRnyaY2iIoyAISCNNXFVUvEtJlBwtiQ/edit?usp=sharing

Example of the code:

function RunFilters() {

  var name = "Adam";

  /*ADAM*/

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Marketing').copyTo(ss);

  /* Before cloning the sheet, delete any previous copy */
  var old_name = ss.getSheetByName(name);
  if (old_name) ss.deleteSheet(old_name); // or old.setName(new Name);

  SpreadsheetApp.flush();
  sheet.setName(name);

  /* Make the new sheet active */
  ss.setActiveSheet(sheet);

      function myFunction() {
          var ss = SpreadsheetApp.getSheetByName('Marketing'); //replace with source Sheet tab name
          var range = ss.getRange('A:I'); //assign the range you want to copy
          var data = range.getValues();

          var tss = SpreadsheetApp.getActiveSpreadsheet(); //replace with destination ID
          var ts = tss.getSheetByName('Adam'); //replace with destination Sheet tab name
          ts.getRange(ts.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);

          var range = ts.getRange(ts.getLastRow() + 1, 1, data.length, data[0].length)
          var rawData = range.getValues()     // get value from spreadsheet 2
          var data = []                       // Filtered Data will be stored in this array

          for (var i = 0; i < rawData.length; i++) {
               if(rawData[i][5] == "Adam")            // Check to see if column K says ipad if not skip it
                    {
                        data.push(rawData[i])
      }
    }
  }
}
Adam Berthiaume
  • 1
  • 1
  • 1
    Could you please fix the formatting of your code? It's unclear if `myFunction` is nested inside `runFilters`. Why do you need to delete and recreate sheets? – Diego Apr 06 '18 at 04:27
  • Sorry about that, yes, the myFunction is nested inside the runFilters. The reason for recreating the sheets is so that when you hit 'runFilters', it will push all of the updated information that was put into the master 'marketing' log, and push it over to the other spread sheets so they have the latest data. From there it would filter the rows to show only the info needed per sheet. Ex. Adam's tab would only show tasks assigned to him, the completed tab would show all tasks that have a date in the 'completed' column. Does that help? – Adam Berthiaume Apr 06 '18 at 12:35
  • 1
    If you just want to make updates in one place, consider just using a `=query` formula on the team member sheets, rather than continually deleting and recreating their sheets. This would then let them use other parts of "their" worksheet for notes on their work, without fear that you will arbitrarily delete their comments. – tehhowch Apr 06 '18 at 12:37

0 Answers0