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;
}
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;
}