Pull a row of data from multiple tabs in Google Sheets and display in one Master Sheet

1
votes

I am looking to create a master sheet within a Google Sheets document which pulls a row of data (C33:AC33) from each tab (while excluding the following tabs: Menu, Template, Class List). I need to use a script because the tabs will constantly change and be added to.

I need the new data to be displayed from the 5th row, 2nd column on the master sheet. In the 1st column, I wanted to display the name of the tab the data came from.

Is this possible? I've been looking for hours and this is the closest snippet of code I've found which I think could possibly do what I need however I am a script newbie and don't know if I'm on the right track:

function pullRows() {
  const ss = SpreadsheetApp.getActive();
  const arr = ss
    .getSheets()
    .filter(s => !s.getName().includes('Master', 'Template', 'Class List';))
    .flatMap(s => s.getDataRange(C33:AC33).getValues());
flatten it
  ss.getSheetByName('Master')
    .getRange(5, 2, arr.length, arr[0].length)
    .setValues(arr);
}
1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

I believe your goal as follows.

  • You want to retrieve the values of the cells "C33:AC33" from sheets except for 'Master', 'Template', 'Class List'.
  • You want to put the values to 'Master` sheet. In this case, you want to put each value from each sheet for each row by adding the sheet name to the top column.
  • You want to put those values from the cell "B5".

Modification points:

  • About !s.getName().includes('Master', 'Template', 'Class List';), when you want to use this, please modify it to !['Master', 'Template', 'Class List'].includes(s.getSheetName().
  • About s.getDataRange(C33:AC33).getValues(), getDataRange has not arguments. And C33:AC33 is not string value.
  • In this case, I would like to propose the following flow.
    1. Retrieve the values from each sheet except for 'Master', 'Template', 'Class List'.
    2. Put the final values to Master sheet.

When above points are reflected to your script, it becomes as follows.

Modified script:

function pullRows() {
  // 1. Retrieve the values from each sheet except for 'Master', 'Template', 'Class List'.
  const ss = SpreadsheetApp.getActive();
  const arr = ss
    .getSheets()
    .filter(s => !['Master', 'Template', 'Class List'].includes(s.getSheetName()))
    .map(s => [s.getSheetName(), ...s.getRange("C33:AC33").getValues()[0]]);

  // 2. Put the final values to Master sheet.
  ss.getSheetByName('Master')
    .getRange(5, 2, arr.length, arr[0].length)
    .setValues(arr);
}

Note:

  • When you want to put each value from each sheet for each column by adding the sheet name to the top row, you can also use the following script. Because of my poor English skill, I couldn't understand which you want to achieve. So as the additional script, I added the following script. When you don't use this, please tell me.

      function pullRows() {
    // 1. Retrieve the values from each sheet except for 'Master', 'Template', 'Class List'.
    const ss = SpreadsheetApp.getActive();
    const arr = ss
      .getSheets()
      .filter(s => !['Master', 'Template', 'Class List'].includes(s.getSheetName()))
      .map(s => [s.getSheetName(), ...s.getRange("C33:AC33").getValues()[0]]);

    // 2. Transpose the retrieved values.
    const res = arr[0].map((_, i) => arr.map(r => r[i]));

    // 3. Put the final values to Master sheet.
    ss.getSheetByName('Master')
      .getRange(5, 2, res.length, res[0].length)
      .setValues(res);
  }

References: