Streamline repetitive formula calculation in google sheets

Question

Summary tab contains project name which is the name for remaining tab names in my google sheets as follows:

TabName
Project1
Project2
Project3

There may be up to 20 TabName defined but this is not necessary. All project name tabs has the same format - 1 row contains the weekly start dates and column 1 has the resource names. The following is what the project name tabs would look like -

Project1 tab

Person	1-Mar-2021	8-Mar-2021	15-Mar-2021	22-Mar-2021	29-Mar-2021	5-Apr-2021
John	2	2	4	5	5	5
Jane		1		1	1	1
Abe			5		5

Project2 begins at a different Start Week from Project1 and Project3.

Project2 tab

Person	4-Jan-2021	11-Jan-2021	18-Jan-2021	25-Jan-2021	1-Feb-2021	8-Feb-2021	15-Feb-2021
Joe	2	2	4	5	5	5
Mary		1		1	1	1
Abe			5		5

Project3 begins at a different Start Week from Project1 and Project2.

Project3 tab

Person	8-Feb-2021	15-Feb-2021	22-Feb-2021	1-Mar-2021	8-Mar-2021	15-Mar-2021	22-Mar-2021	29-Mar-2021
Joe	2	2	4	5	5	5
Mary		1		1	1	1
John			5		5
Marg						3	3	3

Note, the start week in Row1Col2 may not be the same date for all projects. For example, Project1 starts on Mar 1 while Project2 starts on Jan 4. Also, not all resources are across all projects (e.g. Jane and Marg are only in one project).

In the summary tab, it should show the concatenation of all projects by resource and week as follows -

Resource Weekly tab

Person	4-Jan-2021	11-Jan-2021	18-Jan-2021	25-Jan-2021	1-Feb-2021	8-Feb-2021	15-Feb-2021	22-Feb-2021	1-Mar-2021	8-Mar-2021	15-Mar-2021	22-Mar-2021	29-Mar-2021	5-Apr-2021
John								Project3 (5)	Project1 (2)	Project1 (2) Project3 (5)	Project1 (4)	Project1 (5)	Project1 (5)	Project1 (5)
Jane										Project1 (1)		Project1 (1)	Project1 (1)
Joe	Project2 (2)	Project2 (2)	Project2 (4)	Project2 (5)	Project2 (5)	Project2 (5) Project3 (2)	Project3 (2)	Project3 (4)	Project3 (5)	Project3 (5)	Project3 (5)	Project3 (5)
Abe			Project2 (5)		Project2 (5)						Project1 (5)		Project1 (5)
Mary		Project2 (1)		Project2 (1)	Project2 (1)	Project2 (1)	Project3 (1)		Project3 (1)	Project3 (1)	Project3 (1)
Marg											Project3 (3)	Project3 (3)	Project3 (3)

Column 1, which contains the resource name and row 1 with the start dates are fixed (i.e. no calculation required). Also, where a resource has more than one projects, the concatenated projects are separated by a line return.

I would also like that the concatenation calculation does not perform if the Start Week does not have a date OR if there is no resource name defined for that row. The Summary tab will identify the number of project tabs that exists where the concatenation calculation needs to look for data.

I do have the Resource Weekly tab working using a very long SUMPRODUCT and INDIRECT. However, the google sheet is now running much slower when I added this Resource Weekly sheet.

Is there a way to do this using app script?

Answer 1

I believe your goal as follows.

• You want to achieve the following situation using Google Apps Script. (The following result is from my sample script.)

  • From

    

  • To

    

In this case, I would like to propose the following flow.

1. Retrieve sheet names from "Summary" sheet.
2. Retrieve values from each project sheet and create an object for creating result values.
3. Create an result values for putting to "Weekly" sheet.
4. Put the result values to "Weekly" sheet.

When above flow is reflected to a script, it becomes as follows.

Sample script:

function myFunction() {
  // 1. Retrieve sheet names from "Summary" sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName("Summary");
  const sheetNames = summarySheet.getRange("A2:A" + summarySheet.getLastRow()).getValues();

  // 2. Retrieve values from each project sheet and create an object for creating result values.
  const obj = sheetNames.reduce((o, s) => {
    const [[, ...header], ...values] = ss.getSheetByName(s).getDataRange().getValues();
    header.forEach((h, i) => {
      const k = h.toISOString();
      if (o[k]) {
        values.forEach(v => {
          if (v[i + 1].toString() != "") o[k][v[0]] = (o[k][v[0]] ? `${o[k][v[0]]}\n` : "") + `${s} (${Number(v[i + 1])})`;
        });
      } else {
        o[k] = values.reduce((o2, v) => {
          if (v[i + 1].toString() != "") o2[v[0]] = `${s} (${Number(v[i + 1])})`;
          return o2;
        }, {});
      }
    });
    return o;
  }, {});

  // 3. Create an result values for putting to "Weekly" sheet.
  const weeklySheet = ss.getSheetByName("Weekly");
  const [[, ...header], ...values] = weeklySheet.getDataRange().getValues();
  const tempValues = header.reduce((ar, h) => {
    const k = h.toISOString();
    const temp = [];
    if (obj[k]) {
      values.forEach(([v]) => temp.push(obj[k][v] || ""));
      ar.push(temp);
    }
    return ar;
  }, []);
  const res = tempValues[0].map((_, j) => tempValues.map(row => row[j]));

  // 4. Put the result values to "Weekly" sheet.
  weeklySheet.getRange(2, 2, res.length, res[0].length).setValues(res);
}

• When this sample script is used for your sample Spreadsheet, the result I showed in above images can be obtained.

Note:

• When I saw your updated sample Spreadsheet including the result sheet you expect, I noticed that the 1st row of "Weekly" sheet is empty. When I saw your sample Spreadsheet for the 1st time before you updated, the 1st row of "Weekly" sheet was the header row. So I'm confusing this. In this sample script, I prepared it as the situation that the 1st row of "Weekly" sheet is the header row. Please be careful this.
• And also, this sample script is for your sample Spreadsheet. So, if the structure of each sheet of your actual situation is different from your sample Spreadsheet, the script might not be able to be used. Please be careful this.
  • So, please test the sample script using your sample Spreadsheet.

References:

• getValues()
• setValues(values)
• reduce()
• map()

Added:

It seems that in your sample Spreadsheet, there are 2 kinds of Spreadsheets.

• One is that the 1st row of "Weekly" sheet is NOT empty.
• Another is that the 1st row of "Weekly" sheet is empty.

By this, I'm confusing about your situation. My above sample script is for the former situation. For example, when your current situation is changed to the latter situation, please modify above script as follows.

From:

  const [[, ...header], ...values] = weeklySheet.getDataRange().getValues();
  const tempValues = header.reduce((ar, h) => {
    const k = h.toISOString();
    const temp = [];
    if (obj[k]) {
      values.forEach(([v]) => temp.push(obj[k][v] || ""));
      ar.push(temp);
    }
    return ar;
  }, []);
  const res = tempValues[0].map((_, j) => tempValues.map(row => row[j]));

  // 4. Put the result values to "Weekly" sheet.
  weeklySheet.getRange(2, 2, res.length, res[0].length).setValues(res);

To:

  const [,[, ...header], ...values] = weeklySheet.getDataRange().getValues();  // Modified
  const tempValues = header.reduce((ar, h) => {
    const k = h.toISOString();
    const temp = [];
    if (obj[k]) {
      values.forEach(([v]) => temp.push(obj[k][v] || ""));
      ar.push(temp);
    }
    return ar;
  }, []);
  const res = tempValues[0].map((_, j) => tempValues.map(row => row[j]));

  // 4. Put the result values to "Weekly" sheet.
  weeklySheet.getRange(3, 2, res.length, res[0].length).setValues(res);  // Modified