Most efficient way of copying data from tabs to mastersheet in Google Sheets with Google Apps Scripts

Question

I have a spreadsheet where I import GA4 stats on a daily basis for our products in our 'webshop'. For every step in the funnel (it is a travel company) I have a tab where the stats for that specific step are being imported.

Beside that I have one 'masterfile' in the spreadsheet where I want to copy the data to from the other tabs.

The masterfile ("Stats of Product IDs) has the following structure: Column A: Product IDs - unique list of all Product IDs Column B-G: Funnel steps for views Column H-M: Funnel steps for sessions

The tabs where I have to copy the data from ("GA4 packages url", "GA4 reservation url dates", "GA4 reservation url rooms", "GA4 reservation url flights", "GA4 reservation url options", "GA4 reservation url checkout" have the following structure: Column A: Product ID (can have multiple of the same Product IDs) Column B: fullPageUrl Column C: screenPageViews Column D: sessions

Now I use this script (thanks to gpt). At first it looks like it is working but I have the feeling that due to quotas the script is being stalled. If I run it for a second time it is saying completed while less than half of the data is copied to the mastersheet. The question is, does anyone have a better and more efficient way of operating this task?

function copyDataToMasterFile() {
  var masterFileSheetName = "Stats of Product IDs";
  var batchSize = 100; // Adjust the batch size as needed
  
  var masterFile = SpreadsheetApp.getActiveSpreadsheet();
  var masterFileSheet = masterFile.getSheetByName(masterFileSheetName);
  
  var tabMappings = {
    "GA4 packages url": { sourceColumn: "C", destinationColumn: "B" },
    "GA4 reservation url dates": { sourceColumn: "C", destinationColumn: "C" },
    "GA4 reservation url rooms": { sourceColumn: "C", destinationColumn: "D" },
    "GA4 reservation url flights": { sourceColumn: "C", destinationColumn: "E" },
    "GA4 reservation url options": { sourceColumn: "C", destinationColumn: "F" },
    "GA4 reservation url checkout": { sourceColumn: "C", destinationColumn: "G" },
    "GA4 packages url": { sourceColumn: "D", destinationColumn: "H" },
    "GA4 reservation url dates": { sourceColumn: "D", destinationColumn: "I" },
    "GA4 reservation url rooms": { sourceColumn: "D", destinationColumn: "J" },
    "GA4 reservation url flights": { sourceColumn: "D", destinationColumn: "K" },
    "GA4 reservation url options": { sourceColumn: "D", destinationColumn: "L" },
    "GA4 reservation url checkout": { sourceColumn: "D", destinationColumn: "M" }
  };

  for (var tabName in tabMappings) {
    var mapping = tabMappings[tabName];
    var sourceSheet = masterFile.getSheetByName(tabName);
    var sourceData = sourceSheet.getRange("A2:D").getValues();
    var sumData = {};
    
    for (var i = 0; i < sourceData.length; i++) {
      var productId = sourceData[i][0];
      var screenPageViews = sourceData[i][2];
      
      if (!sumData[productId]) {
        sumData[productId] = 0;
      }
      
      sumData[productId] += screenPageViews;
    }
    
    var destinationColumn = getColumnNumber(mapping.destinationColumn);
    var masterFileData = masterFileSheet.getRange("A2:M").getValues();
    
    for (var j = 0; j < masterFileData.length; j++) {
      var masterProductId = masterFileData[j][0];
      
      if (masterProductId && sumData[masterProductId]) {
        var existingValue = masterFileData[j][destinationColumn];
        var newValue = existingValue + sumData[masterProductId];
        masterFileSheet.getRange(j + 2, destinationColumn).setValue(newValue);
      }
    }
    
    // Clear the sumData for each batch to avoid memory buildup
    sumData = {};

    // Pause the execution to stay within quota limits
    Utilities.sleep(500);
  }
}

function getColumnNumber(columnLetter) {
  var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  var columnNumber = 0;
  
  for (var i = 0; i < columnLetter.length; i++) {
    columnNumber += (base.indexOf(columnLetter[i]) + 1) * Math.pow(26, columnLetter.length - i - 1);
  }
  
  return columnNumber;
}

Answer 1

After some tips and retries this script is working at the moment and it looks like it is copying all the data. The thing is, not only has it to be copied but the new data needs to add up with the old data. Here is the script that was working in this case:

function updateStatsOfProductIDs() {
  // Work out the location for every statistic that has to be copied and calculated
  var tabs = [
    { tabName: "GA4 packages url", sourceRange: "A1:D", targetColumn: "B" },
    { tabName: "GA4 reservation url dates", sourceRange: "A1:D", targetColumn: "C" },
    { tabName: "GA4 reservation url rooms", sourceRange: "A1:D", targetColumn: "D" },
    { tabName: "GA4 reservation url flights", sourceRange: "A1:D", targetColumn: "E" },
    { tabName: "GA4 reservation url options", sourceRange: "A1:D", targetColumn: "F" },
    { tabName: "GA4 reservation url checkout", sourceRange: "A1:D", targetColumn: "G" }
  ];

  // Get the active spreadsheet and the "Stats of Product IDs" sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var statsOfProductIDsSheet = spreadsheet.getSheetByName("Stats of Product IDs");

  // Loop through each tab and update the master sheet
  tabs.forEach(function (tab) {
    // Get the source tab and range
    var sourceSheet = spreadsheet.getSheetByName(tab.tabName);
    var sourceRange = sourceSheet.getRange(tab.sourceRange);

    // Get the data from the source range
    var data = sourceRange.getValues();

    // Create an object to store the sums for each product ID
    var sums = {};

    // Loop through the data and calculate the sums
    for (var i = 1; i < data.length; i++) {
      var productID = data[i][0];
      var screenPageViews = data[i][2];

      if (productID && screenPageViews) {
        // Check if the product ID exists in the sums object
        if (sums.hasOwnProperty(productID)) {
          // Add the screenPageViews to the existing sum
          sums[productID] += screenPageViews;
        } else {
          // Initialize the sum for a new product ID
          sums[productID] = screenPageViews;
        }
      }
    }

    // Get the data from the "Stats of Product IDs" sheet
    var statsData = statsOfProductIDsSheet.getDataRange().getValues();

    // Loop through the data and update the sums in the target column
    for (var j = 1; j < statsData.length; j++) {
      var productID = statsData[j][0];

      if (productID && sums.hasOwnProperty(productID)) {
        // Get the current value in the target column
        var columnIndex = getColumnIndex(tab.targetColumn);
        var currentValue = statsData[j][columnIndex] || 0;

        // Add the sum to the current value
        var newValue = currentValue + sums[productID];

        // Update the value in the target column
        statsOfProductIDsSheet.getRange(j + 1, columnIndex + 1).setValue(newValue);
      }
    }
  });
}

// Helper function to get the column index based on the column letter
function getColumnIndex(columnLetter) {
  return columnLetter.charCodeAt(0) - 65;
}