Question

0

315

Views

Google Sheets, stack report from multiple workbooks

Goal: To stack data from 90+ google workbooks, all with the same sheet name, into the one master sheet for reporting

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.

I am trying to build an array then paste it in one go.

The tables I am stacking have 47 columns, unknown number of rows. The part that opens the sheets is all working perfectly.

    // Get the data from the worksheets
    var indexsheet = SpreadsheetApp.getActive().getSheetByName("Index");
    var outputsheet = SpreadsheetApp.getActive().getSheetByName("Output");
    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)

    };

This is the index tab where we are getting the workbookid from to open each file

This is the output file, we are stacking all data from each country

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-03T03:13:58.951Z

I believe your goal is as follows.

You want to retrieve the Spreadsheet IDs from the column "G" of "Index" sheet.
You want to give the specific sheet name using a dialog.
You want to retrieve all values from the specification sheet in all Spreadsheets. In this case, you want to remove the header row.
You want to put the retrieved values on "Output" sheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var indexsheet = ss.getSheetByName("Index");
  var outputsheet = ss.getSheetByName("Output");
  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();

  var ids = indexsheet.getRange("G1:G" + indexsheet.getLastRow()).getValues();
  var values = ids.reduce((ar, [id]) => {
    try {
      var [, ...values] = SpreadsheetApp.openById(id).getSheetByName(CurrentCycleName).getDataRange().getValues();
      ar = [...ar, ...values];
    } catch (e) {
      console.log(`"${id}" was not found.`);
    }
    return ar;
  }, []);
  if (values.length == 0) return;
  
  // If the number of columns is different in all Spreadsheets, please use the following script.
  // var maxLen = Math.max(...values.map(r => r.length));
  // values = values.map(r => r.length < maxLen ? [...r, ...Array(maxLen - r.length).fill("")] : r);

  outputsheet.getRange(outputsheet.getLastRow() + 1, 1, values.length, values[1].length).setValues(values);
}

Note:

When the number of Spreadsheet IDs is large, the processing time might be over 6 minutes. I'm worried about this. At that time, how about separating the Spreadsheet IDs?

Reference:

reduce()

0

315