title: Google Sheets, stack report from multiple workbooks
body: <p>Goal: To stack data from 90+ google workbooks, all with the same sheet name, into the one master sheet for reporting</p>
  <p>Info:
  All worksheets have the same number of columns.
  I have the following script but it does not run properly, I think the issue is with how I am caching / Pushing the data to the array before pasting to the output sheet.</p>
  <p>I am trying to build an array then paste it in one go.</p>
  <p>The tables I am stacking have 47 columns, unknown number of rows.
  The part that opens the sheets is all working perfectly.</p>
  <pre><code>    // Get the data from the worksheets
      var indexsheet = SpreadsheetApp.getActive().getSheetByName(&quot;Index&quot;);
      var outputsheet = SpreadsheetApp.getActive().getSheetByName(&quot;Output&quot;);
      var response = SpreadsheetApp.getUi().prompt('Current Cycle', 'Enter Cycle Name Exactly in YY-MMM-Cycle# format', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL)
      var CurrentCycleName = response.getResponseText()
  
      // Assign datasets to variables
      var indexdata = indexsheet.getDataRange().getValues();
  
  
      // For each workbook in the index sheet, open it and copy the data to a cache
  
      indexdata.forEach(function(row, r) {
              try {
  
                  //open Entity specific workbook
                  var workbookid = indexsheet.getRange(r + 1, 7, 1, 1).getValues();
                  var Entityworkbook = SpreadsheetApp.openById(workbookid)
  
                  // Open workhseet
                  Entitysheet.getSheetByName(CurrentCycleName)
  
                  // Add PR Data to cache - stacking for all countrys
                  var PRDataCache = Entitysheet.getDataRange().push()
  
  
  
  
              } catch {}
    })
      // Set the all values of the sheet at once
          outputsheet.getRange(r + 1, 14).setValue('Issue Splitting Data') 
      Entitysheet.getRange(2, 1, PRDataCache.length || 1, 47).setValues(PRDataCache)
  
      };
  
  </code></pre>
  <p><a href="https://i.stack.imgur.com/QOl9m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QOl9m.png" alt="Index Tab" /></a>
  This is the index tab where we are getting the workbookid from to open each file</p>
  <p><a href="https://i.stack.imgur.com/FkKBJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FkKBJ.png" alt="Output Tab" /></a>
  This is the output file, we are stacking all data from each country</p>