0

Problem: I am working with a Google Sheets file were the first tab is the 'raw export'. Data in this sheet looks basically like this:

Customer Item Cost
Amy Shirt 15.50
Amy Pants 20.00
Amy Socks 8.95
Bill Shoes 18.75
Bill Socks 5.99
Chris Shirt 13.65

Now, in the subsequent sheets I have a QUERY function that is grabbing all the results in the 'raw export' sheet relating to a particular customer. The customer is defined by the sheet name. So there is a tab for "Amy", "Bill", etc.

=QUERY(A:C, "SELECT * WHERE Col1 = '"&sheetName()&"'", 1)

The sheetName() is a simple function I found from this source.

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

It works correctly for the initial sheet. The problem is the 'raw export' tab will keep receiving new data over time as most customers and purchases come in. So every time I duplicate the tab and change the tab name to add a new customer, the formula does not update. Thus, the query function does not show data for the new customer name referenced in the sheet name.

I am going to keep adding new tabs for new customers over time so it would be great if all I had to do was duplicate the tab and change the sheet name. Then the rest would populate accordingly.

Steps I Tried to Resolve: I tried setting up a Trigger that corresponds to any change, but it did not work.

Google Sheet App Scripts Trigger

Guy Dotan
  • 1
  • 2
  • you could try `IF(NOW(),QUERY(...))`. NOW is a volatile function so it updates every time the sheet is modified. – z'' Jul 12 '23 at 20:34
  • @ztiaa - thanks for the suggestion! I dont think this will work because the underlying issue is that Google Sheets does not recognize the action of changing a sheet name as a valid event to indicate a modification to the document. – Guy Dotan Jul 12 '23 at 21:51
  • In your situation, how about refreshing the functions using a Google Apps Script? In order to understand it, is this thread useful? https://stackoverflow.com/q/58579114 But, if you cannot refresh the functions in your actual situation, I apologize. – Tanaike Jul 13 '23 at 00:45
  • @Tanaike - thanks for that resource. I can look into it but I believe this same issue will occur where a sheet name change would not re-run the formula, even with an onChange function applied. appreciate the idea nonethless! – Guy Dotan Jul 13 '23 at 19:38
  • Thank you for replying. About `onChange function applied.`, I apologize for my poor English skill. Unfortunately, from your question, I couldn't notice it. – Tanaike Jul 13 '23 at 22:56

2 Answers2

0

I recommend changing it up a bit :) Here's a gif showing how it works

enter image description here

I created a Drop-down menu with customer names in cell B2 and when selecting any name, the function looks for a tab under that name

and the formula looks like this

=QUERY(INDIRECT("'"&B2&"'!A2:C"), "select *", 0)

so you can remove the script and work on functions

ps. I didn't add the Chris tab to show that the function doesn't search in raw data :)

I hope I helped

P G
  • 21
  • 5
  • Thanks for the support and very clear example. I ended up using your advice but tweaking it for my use case. Your example doesn't solve the issue of duplicating the tab and the data refreshes based on the new tab name. However, what I did do is a add a dropdown menu chip on each tab. And instead of referencing the sheet name with sheetName() function, I just referenced the dropdown toggle. That made it pretty simple to duplicate the tab, change the customer name from the dropdown menu on the new sheet, and then all the data repopulates based on the new name. – Guy Dotan Jul 13 '23 at 19:41
0

You could automate even further. If I understand correctly, an ideal flow would be:

  1. Manually update the 'raw export' sheet however you do it today.
  2. Run a script that a) determines if there is a tab for every unique customer name in col 1 of 'raw export'; b) if not, creates a new tab and names it with new customer name; c) inserts a formula in the new sheet that filters all the content in 'raw export' to show just this customer's data.

If I've got that right, try the script below. It assumes first tab is named 'raw export'.

/**
 * @OnlyCurrentDoc
 */

function makeNewCustomerTabs() {
  const rawSheet = SpreadsheetApp.getActive().getSheetByName("raw export"); //change if sheet name changes
  const lastRow = rawSheet.getLastRow(); //used in range definition for better performance
  const customers = rawSheet.getRange(2,1,lastRow-1,1).getValues().flat(); //get 1D array of customer names up to last row with data
  for (var i = 0; i <customers.length; i++) {
    var sheets = allSheetNames(); // calls helper function below to return all sheet names including new sheets created during this loop
    if(sheets.indexOf(customers[i]) == -1) { // if no sheet exists for a customer name, create one and add formula to A1 on the new sheet
      var newSheetIndex = sheets.length;
      var newSheetName = customers[i];
      // Create new sheet for this customer name at end of current tabs      
      SpreadsheetApp.getActive().insertSheet(newSheetName,newSheetIndex);
      var custSheet = SpreadsheetApp.getActive().getSheetByName(newSheetName);
      var range = custSheet.getRange("A1");
      // Add headers and filtering formula to the new sheet to show just this customer's activity
      range.setFormula("={\"Customer\",\"Item\",\"Cost\";FILTER('raw export'!A1:C,'raw export'!A1:A=" + "\"" + newSheetName +"\"" + ")}"); 
    }
  }
}

// Helper function for the makeNewCustomerTabs script above
function allSheetNames() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  });
  return sheetNames;
}
dwides
  • 46
  • 1
  • 1