Scripting with Google Apps to consolidate multiple spreadsheets into a main document

Question

Scripting with Google Apps to consolidate multiple spreadsheets into a main document

I manage a small sales team (currently 5 members but growing) who input sales data into multiple sheets within one workbook. I am looking to automate the process by running a script to merge all these sheets on a daily basis.

Here is the breakdown of the daily stages:

1) Clear all existing records from the Master Daily sheet (to ensure any updates or changes are accounted for from individual sales sheets) 2) Iterate through each sheet and consolidate the data

There are 6 columns, all in the same order but with different data values.

View Demo Sheet Here

Edit Open until bounty awarded.

Code snippet attempt :

function merge() {
    var v, arr,
        ss = SpreadsheetApp.getActive();
    ss.getSheets().filter(function(s) {
        return s.getName()
            .indexOf('Project') > -1
    }).forEach(function(s, i) {
        v = s.getDataRange()
            .getValues()
            .filter(function(r) {
                return r.toString()
                    .length > 0
            })
        arr = (i == 0) ? v : arr.concat(v);
    })
    ss.getSheetByName('Master')
        .getRange(1, 1, arr.length, arr[0].length)
        .setValues(arr);
}

Any assistance would be greatly appreciated.

Thank you!

javascript google-apps-script google-sheets

Answer 1

Answer №1

Here is a simplified version of your script:

//@OnlyCurrentDoc
function mergeData() {
  const spreadsheet = SpreadsheetApp.getActive();
  const data = spreadsheet
    .getSheets()
    .filter(sheet => !sheet.getName().includes('Master'))//excluding Master sheet
    .flatMap(sheet => sheet.getDataRange().getValues());//mapping sheets to values and flattening it
  spreadsheet.getSheetByName('Master')
    .getRange(1, 1, data.length, data[0].length)
    .setValues(data);
}

Updates Made:

Excluded the master sheet instead of including all project sheets
Optimized using v8 es6 syntax

Answer 2

Here is a simplified version of your script:

//@OnlyCurrentDoc
function mergeData() {
  const spreadsheet = SpreadsheetApp.getActive();
  const data = spreadsheet
    .getSheets()
    .filter(sheet => !sheet.getName().includes('Master'))//excluding Master sheet
    .flatMap(sheet => sheet.getDataRange().getValues());//mapping sheets to values and flattening it
  spreadsheet.getSheetByName('Master')
    .getRange(1, 1, data.length, data[0].length)
    .setValues(data);
}

Updates Made:

Excluded the master sheet instead of including all project sheets
Optimized using v8 es6 syntax

Answer 3

Answer №2

Check out the SheetGo add-on for a quick and efficient way to complete tasks with just a few clicks.

Take a look at the following link:

Answer 4

Check out the SheetGo add-on for a quick and efficient way to complete tasks with just a few clicks.

Take a look at the following link:

Answer 5

Answer №3

The following sections outline the main functions:

dailysheets.gs:

function archiveDaily() {
  // Function to archive daily sheets
}

function createDailyTrigger() {
  // Function to create a daily trigger
}

These files serve as supportive assets for various projects.

triggers.gs:

//Filename: triggers.gs

// Functions to handle project triggers.
function deleteTrigger(triggerName){
  // Logic to delete specific trigger
}

function isTrigger(funcName){
  // Check if trigger exists for specified function
}

function deleteAllTriggers(){
  // Delete all project triggers
}

function displayProjectTriggers() {
  // Display all project triggers
}

globals.gs:

The 'Globals' sheet stores essential information and acts as a reference for other processes in the projects.

function getGlobals(){
  // Function to retrieve global variables
}

function setGlobals(dfltObj){
  // Function to set default global values
}

function getGlobal(name){
  // Get global value by name
}

function setGlobal(name,value){
  // Set global value by name
}

function cleanGlobals() {
  // Function to maintain the cleanliness of Globals sheet
}

Codes.gs:

This section includes a predefined menu setup.

function onOpen(){
  // Create custom menu on opening
}

function makeMenu(){
  // Generate custom menu
}

Examples of data sheets and images can be found in the provided links.

Answer 6

The following sections outline the main functions:

dailysheets.gs:

function archiveDaily() {
  // Function to archive daily sheets
}

function createDailyTrigger() {
  // Function to create a daily trigger
}

These files serve as supportive assets for various projects.

triggers.gs:

//Filename: triggers.gs

// Functions to handle project triggers.
function deleteTrigger(triggerName){
  // Logic to delete specific trigger
}

function isTrigger(funcName){
  // Check if trigger exists for specified function
}

function deleteAllTriggers(){
  // Delete all project triggers
}

function displayProjectTriggers() {
  // Display all project triggers
}

globals.gs:

The 'Globals' sheet stores essential information and acts as a reference for other processes in the projects.

function getGlobals(){
  // Function to retrieve global variables
}

function setGlobals(dfltObj){
  // Function to set default global values
}

function getGlobal(name){
  // Get global value by name
}

function setGlobal(name,value){
  // Set global value by name
}

function cleanGlobals() {
  // Function to maintain the cleanliness of Globals sheet
}

Codes.gs:

This section includes a predefined menu setup.

function onOpen(){
  // Create custom menu on opening
}

function makeMenu(){
  // Generate custom menu
}

Examples of data sheets and images can be found in the provided links.

Answer 7

Answer №4

This script automates the process of archiving daily data from multiple tabs into a master tab in Google Sheets. Here's what it does:

1) Archives daily data regardless of naming conventions on other tabs, focusing only on correct naming for the master tab.

2) Skips header rows on other tabs assuming the master tab contains headers.

3) Triggers the script to run every day.

4) Includes a feature to archive the previous day's master data in case the script runs before accessing new data, storing only one day of archived data.

The EverydayTrigger function sets up the daily loop, while the MergeSheet function is responsible for merging the sheets. Detailed explanations are provided within the script itself for each line of code.

function EverydayTrigger() {
  ScriptApp.newTrigger("MergeSheets")
  .timeBased()
  .everyDays(1)
  .create();
}

function MergeSheets() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var data = null;
  var RetrieveSheet = null;
  var PasteSheet = ss.getSheetByName("Master");

  /*
  // To use the archiving option
  var PrevArchSheetName = PasteSheet.getRange(1,1000).getValue();

  if(PrevArchSheetName.length > 1){
    var PrevArchSheet = ss.getSheetByName(PrevArchSheetName); 
    ss.deleteSheet(PrevArchSheet);
  }
  */

  var sheets = ss.getSheets();//get all sheets regardless of naming conventions

  /*
  // For archiving the previous day's data
  var ArchiveSheet = ss.insertSheet(new Date() + "Master");
  PasteSheet.getDataRange().copyTo(ArchiveSheet.getRange(1,1));
  PasteSheet.getRange(1,1000).setValue(ArchiveSheet.getName()); 
  */

  PasteSheet.getRange(2,1,PasteSheet.getLastRow(),PasteSheet.getLastColumn()).clear();//remove old information

  for (var i =0; i<sheets.length; i++){
    RetrieveSheet = ss.getSheetByName(sheets[i].getName());
    if (RetrieveSheet.getName() != 'Master'){
      // data = RetrieveSheet.getDataRange(); 
      data = RetrieveSheet.getRange(2,1,RetrieveSheet.getLastRow(),RetrieveSheet.getLastColumn()); 
      data.copyTo(PasteSheet.getRange(parseInt(PasteSheet.getLastRow())+1,1));
    }
  }
}

Answer 8

This script automates the process of archiving daily data from multiple tabs into a master tab in Google Sheets. Here's what it does:

1) Archives daily data regardless of naming conventions on other tabs, focusing only on correct naming for the master tab.

2) Skips header rows on other tabs assuming the master tab contains headers.

3) Triggers the script to run every day.

4) Includes a feature to archive the previous day's master data in case the script runs before accessing new data, storing only one day of archived data.

The EverydayTrigger function sets up the daily loop, while the MergeSheet function is responsible for merging the sheets. Detailed explanations are provided within the script itself for each line of code.

function EverydayTrigger() {
  ScriptApp.newTrigger("MergeSheets")
  .timeBased()
  .everyDays(1)
  .create();
}

function MergeSheets() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var data = null;
  var RetrieveSheet = null;
  var PasteSheet = ss.getSheetByName("Master");

  /*
  // To use the archiving option
  var PrevArchSheetName = PasteSheet.getRange(1,1000).getValue();

  if(PrevArchSheetName.length > 1){
    var PrevArchSheet = ss.getSheetByName(PrevArchSheetName); 
    ss.deleteSheet(PrevArchSheet);
  }
  */

  var sheets = ss.getSheets();//get all sheets regardless of naming conventions

  /*
  // For archiving the previous day's data
  var ArchiveSheet = ss.insertSheet(new Date() + "Master");
  PasteSheet.getDataRange().copyTo(ArchiveSheet.getRange(1,1));
  PasteSheet.getRange(1,1000).setValue(ArchiveSheet.getName()); 
  */

  PasteSheet.getRange(2,1,PasteSheet.getLastRow(),PasteSheet.getLastColumn()).clear();//remove old information

  for (var i =0; i<sheets.length; i++){
    RetrieveSheet = ss.getSheetByName(sheets[i].getName());
    if (RetrieveSheet.getName() != 'Master'){
      // data = RetrieveSheet.getDataRange(); 
      data = RetrieveSheet.getRange(2,1,RetrieveSheet.getLastRow(),RetrieveSheet.getLastColumn()); 
      data.copyTo(PasteSheet.getRange(parseInt(PasteSheet.getLastRow())+1,1));
    }
  }
}

Scripting with Google Apps to consolidate multiple spreadsheets into a main document

Answer №1

Answer №2

Answer №3

Answer №4

Similar questions

I'm attempting to store this HTML code in local storage in order to utilize it on a new cart page, but I keep encountering an error

I'm experiencing difficulty displaying my nested array in JavaScript

Information on the Manufacturer of Devices Using React Native

Performing an Ajax GET request in jQuery with custom headers and displaying the response in a new tab/window

Invoke multiple stored functions using JavaScript, beginning with this one

How to incorporate click and drag feature for rotating a 3D object using vue.js

Jquery's ajax function is failing to execute the server side function

Issue with Bootstrap alert persisting even after being displayed once

What steps should I take to modify the API addresses within the React project build?

Limit the frequency of rendering JSX elements in React by implementing debouncing techniques

The React apexchart heatmap's height remains static despite attempting to change it through state updates

Troubleshooting Node.js Application: Investigating Port Errors

Manually closing Bootstrap 4 Popper.js Tooltip on command

What is the best way to limit the containment property of the sortable feature to a specific div

Cease the audio from playing unless you are actively hovering over the image

Sails encountering CORS preflight error due to cross-origin request

What is the best method for creating numerous Popovers within a single ID?

`Can you explain how to specify the elements of an array within a form using AngularJS?`

obtain data from an array using Angular 5

Use jQuery to update the field without affecting the observable