Getting error: "value too long for type character varying(5000)" saving custom function

How To
,
1

I have a custom function which has no errors in syntax, but when I try to save it, Make throws an ‘Unknown exception’ with message “value too long for type character varying(5000)”. I am not sure what validation Make is running on saving of the function. Has anyone faced this issue before? My function is pasted below:

function GroupByApp(inputArray){
   
    const debugMode = 1;
   
    const appFriendlyNames = {
        'olk.exe': 'Outlook',
        'POWERPNT.EXE': 'PowerPoint',
        'ms-teams.exe': 'Microsoft Teams',
        'EXCEL.EXE': 'Excel',
        'slack.exe': 'Slack',
        'explorer.exe': 'File Explorer',
        'WINWORD.EXE': 'Word',
        'CamtasiaStudio.exe': 'Camtasia Video Editor',
        'SnagitEditor.exe': 'SnagIt',
        'WhatsApp.exe': 'WhatsApp Web',
        'Microsoft.Media.Player.exe': 'Media Player'
    };

    let employeeData = [];
    let today = new Date();
    today.setHours(0, 0, 0, 0); // Set to start of today
    let yesterday = new Date(today);
    yesterday.setDate(yesterday.getDate() - 1); // Set to start of yesterday

    inputArray.forEach(collection => {
        let logTime = new Date(collection.time);
        logTime.setHours(0, 0, 0, 0); // Set to start of the log's date

        // Process only if the log is from yesterday
        if (logTime.getTime() === yesterday.getTime()) {
            let user = collection.user;
            let userIndex = employeeData.findIndex(u => u.employee === user);

            // Initialize user data if not already present
            if (userIndex === -1) {
                employeeData.push({
                    employee: user,
                    totalDuration: 0,
                    firstLog: collection.time,
                    lastLog: collection.time,
                    apps: [],
                    browserWebsites: []
                });
                userIndex = employeeData.length - 1;
            }

            let userObject = employeeData[userIndex];
            let duration = parseFloat(collection.duration); // Convert duration to a number

            // Update total duration regardless of the duration of the individual activity
            if (!isNaN(duration)) {
                userObject.totalDuration += duration;
            }

            // Update firstLog and lastLog
            let currentTime = new Date(collection.time);
            if (new Date(userObject.firstLog) > currentTime) {
                userObject.firstLog = collection.time;
            }
            if (new Date(userObject.lastLog) < currentTime) {
                userObject.lastLog = collection.time;
            }

        }

        let app = collection.executable;
        let friendlyAppName = appFriendlyNames[app] || app; // Translate to friendly name

        // In debug mode, add all apps and websites without filtering
        if (debugMode === 1) {
            let appIndex = userObject.apps.findIndex(a => a.name === friendlyAppName);
            if (appIndex === -1) {
                userObject.apps.push({ name: friendlyAppName, duration: duration });
            } else {
                userObject.apps[appIndex].duration += duration;
            }

            if (app === 'chrome.exe' || app === 'msedge.exe') {
                let website = collection.website || 'Unknown';
                let websiteIndex = userObject.browserWebsites.findIndex(w => w.name === website);
                if (websiteIndex === -1) {
                    userObject.browserWebsites.push({ name: website, duration: duration });
                } else {
                    userObject.browserWebsites[websiteIndex].duration += duration;
                }
            }
        } else {
            // Normal mode with filters
            // Handle browser separately for productivity -1, 0, and 1
            if ((app === 'chrome.exe' || app === 'msedge.exe') && (collection.productivity === -1 || collection.productivity === 0 || collection.productivity === 1)) {
                let website = collection.website || 'Unknown';
                // Exclude 'activtrak.com' domain
                if (!website.includes('activtrak.com')) {
                    let websiteIndex = userObject.browserWebsites.findIndex(w => w.name === website);
                    if (websiteIndex === -1) {
                        userObject.browserWebsites.push({ name: website, duration: duration });
                    } else {
                        userObject.browserWebsites[websiteIndex].duration += duration;
                    }
                }
            } else if ((collection.productivity === 0 || collection.productivity === 1) && duration >= 60) {
                // Process apps only for productivity 0 and 1
                let appIndex = userObject.apps.findIndex(a => a.name === friendlyAppName);
                if (appIndex === -1) {
                    userObject.apps.push({ name: friendlyAppName, duration: duration });
                } else {
                    userObject.apps[appIndex].duration += duration;
                }
            }
        }
        
    });

    // Sort apps and browser websites by duration, descending
    employeeData.forEach(userObject => {
        userObject.apps.sort((a, b) => b.duration - a.duration);
        if (userObject.browserWebsites.length > 0) {
            userObject.browserWebsites.sort((a, b) => b.duration - a.duration);
        }
    });

    return employeeData;


}
2

You function is 5247 characters long.

You could remove the indentation of the code to reduce the whitespace:

function GroupByApp(inputArray){
const debugMode = 1;
const appFriendlyNames = {
'olk.exe': 'Outlook',
'POWERPNT.EXE': 'PowerPoint',
'ms-teams.exe': 'Microsoft Teams',
'EXCEL.EXE': 'Excel',
'slack.exe': 'Slack',
'explorer.exe': 'File Explorer',
'WINWORD.EXE': 'Word',
'CamtasiaStudio.exe': 'Camtasia Video Editor',
'SnagitEditor.exe': 'SnagIt',
'WhatsApp.exe': 'WhatsApp Web',
'Microsoft.Media.Player.exe': 'Media Player'
};
let employeeData = [];
let today = new Date();
today.setHours(0, 0, 0, 0); // Set to start of today
let yesterday = new Date(today);
yesterday.setDate(yesterday.getDate() - 1); // Set to start of yesterday
inputArray.forEach(collection => {
let logTime = new Date(collection.time);
logTime.setHours(0, 0, 0, 0); // Set to start of the log's date
// Process only if the log is from yesterday
if (logTime.getTime() === yesterday.getTime()) {
let user = collection.user;
let userIndex = employeeData.findIndex(u => u.employee === user);
// Initialize user data if not already present
if (userIndex === -1) {
employeeData.push({
employee: user,
totalDuration: 0,
firstLog: collection.time,
lastLog: collection.time,
apps: [],
browserWebsites: []
});
userIndex = employeeData.length - 1;
}
let userObject = employeeData[userIndex];
let duration = parseFloat(collection.duration); // Convert duration to a number
// Update total duration regardless of the duration of the individual activity
if (!isNaN(duration)) {
userObject.totalDuration += duration;
}
// Update firstLog and lastLog
let currentTime = new Date(collection.time);
if (new Date(userObject.firstLog) > currentTime) {
userObject.firstLog = collection.time;
}
if (new Date(userObject.lastLog) < currentTime) {
userObject.lastLog = collection.time;
}
}
let app = collection.executable;
let friendlyAppName = appFriendlyNames[app] || app; // Translate to friendly name
// In debug mode, add all apps and websites without filtering
if (debugMode === 1) {
let appIndex = userObject.apps.findIndex(a => a.name === friendlyAppName);
if (appIndex === -1) {
userObject.apps.push({ name: friendlyAppName, duration: duration });
} else {
userObject.apps[appIndex].duration += duration;
}
if (app === 'chrome.exe' || app === 'msedge.exe') {
let website = collection.website || 'Unknown';
let websiteIndex = userObject.browserWebsites.findIndex(w => w.name === website);
if (websiteIndex === -1) {
userObject.browserWebsites.push({ name: website, duration: duration });
} else {
userObject.browserWebsites[websiteIndex].duration += duration;
}
}
} else {
// Normal mode with filters
// Handle browser separately for productivity -1, 0, and 1
if ((app === 'chrome.exe' || app === 'msedge.exe') && (collection.productivity === -1 || collection.productivity === 0 || collection.productivity === 1)) {
let website = collection.website || 'Unknown';
// Exclude 'activtrak.com' domain
if (!website.includes('activtrak.com')) {
let websiteIndex = userObject.browserWebsites.findIndex(w => w.name === website);
if (websiteIndex === -1) {
userObject.browserWebsites.push({ name: website, duration: duration });
} else {
userObject.browserWebsites[websiteIndex].duration += duration;
}
}
} else if ((collection.productivity === 0 || collection.productivity === 1) && duration >= 60) {
// Process apps only for productivity 0 and 1
let appIndex = userObject.apps.findIndex(a => a.name === friendlyAppName);
if (appIndex === -1) {
userObject.apps.push({ name: friendlyAppName, duration: duration });
} else {
userObject.apps[appIndex].duration += duration;
}
}
}
});
// Sort apps and browser websites by duration, descending
employeeData.forEach(userObject => {
userObject.apps.sort((a, b) => b.duration - a.duration);
if (userObject.browserWebsites.length > 0) {
userObject.browserWebsites.sort((a, b) => b.duration - a.duration);
}
});
return employeeData;
}

This reduces it to 3819 characters long, and you will be able to save it now.

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

1 Like
3

To reduce the number of characters of JavaScript code in future,

here are some possible things you can do:

  • use shorter variable names
  • remove indentation, or reduce to single spaces
  • remove unnecessary newlines
  • remove semicolons where possible
  • instead of if-else statements, use inline conditional (ternary) operator

You can also use tools like JavaScript minifiers to compact the code further.

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

2 Likes
4

Thanks @samliew - I did not know that there was a limit of 5000 characters for a script. I actually managed to solve it in a different way. I used a JS validator (https://validatejavascript.com/) and then copied their output which didn’t look different to me, and it managed to save it. I wonder if they removed some characters, it doesn’t look like they did. See the script below:

function GroupByApp(inputArray) {
  const debugMode = 1;

  const appFriendlyNames = {
    'olk.exe': 'Outlook',
    'POWERPNT.EXE': 'PowerPoint',
    'ms-teams.exe': 'Microsoft Teams',
    'EXCEL.EXE': 'Excel',
    'slack.exe': 'Slack',
    'explorer.exe': 'File Explorer',
    'WINWORD.EXE': 'Word',
    'CamtasiaStudio.exe': 'Camtasia Video Editor',
    'SnagitEditor.exe': 'SnagIt',
    'WhatsApp.exe': 'WhatsApp Web',
    'Microsoft.Media.Player.exe': 'Media Player',
  };

  const employeeData = [];
  const today = new Date();
  today.setHours(0, 0, 0, 0); // Set to start of today
  const yesterday = new Date(today);
  yesterday.setDate(yesterday.getDate() - 1); // Set to start of yesterday

  inputArray.forEach((collection) => {
    const logTime = new Date(collection.time);
    logTime.setHours(0, 0, 0, 0); // Set to start of the log's date

    // Process only if the log is from yesterday
    if (logTime.getTime() === yesterday.getTime()) {
      const { user } = collection;
      let userIndex = employeeData.findIndex((u) => u.employee === user);

      // Initialize user data if not already present
      if (userIndex === -1) {
        employeeData.push({
          employee: user,
          totalDuration: 0,
          firstLog: collection.time,
          lastLog: collection.time,
          apps: [],
          browserWebsites: [],
        });
        userIndex = employeeData.length - 1;
      }

      const userObject = employeeData[userIndex];
      const duration = parseFloat(collection.duration); // Convert duration to a number

      // Update total duration regardless of the duration of the individual activity
      if (!isNaN(duration)) {
        userObject.totalDuration += duration;
      }

      // Update firstLog and lastLog
      const currentTime = new Date(collection.time);
      if (new Date(userObject.firstLog) > currentTime) {
        userObject.firstLog = collection.time;
      }
      if (new Date(userObject.lastLog) < currentTime) {
        userObject.lastLog = collection.time;
      }

      const app = collection.executable;
      const friendlyAppName = appFriendlyNames[app] || app; // Translate to friendly name

      // In debug mode, add all apps and websites without filtering
      if (debugMode === 1) {
        const appIndex = userObject.apps.findIndex((a) => a.name === friendlyAppName);
        if (appIndex === -1) {
          userObject.apps.push({ name: friendlyAppName, duration });
        } else {
          userObject.apps[appIndex].duration += duration;
        }

        if (app === 'chrome.exe' || app === 'msedge.exe') {
          const website = collection.website || 'Unknown';
          const websiteIndex = userObject.browserWebsites.findIndex((w) => w.name === website);
          if (websiteIndex === -1) {
            userObject.browserWebsites.push({ name: website, duration });
          } else {
            userObject.browserWebsites[websiteIndex].duration += duration;
          }
        }
      } else {
        // Normal mode with filters
        // Handle browser separately for productivity -1, 0, and 1
        if ((app === 'chrome.exe' || app === 'msedge.exe') && (collection.productivity === -1 || collection.productivity === 0 || collection.productivity === 1)) {
          const website = collection.website || 'Unknown';
          // Exclude 'activtrak.com' domain
          if (!website.includes('activtrak.com')) {
            const websiteIndex = userObject.browserWebsites.findIndex((w) => w.name === website);
            if (websiteIndex === -1) {
              userObject.browserWebsites.push({ name: website, duration });
            } else {
              userObject.browserWebsites[websiteIndex].duration += duration;
            }
          }
        } else if ((collection.productivity === 0 || collection.productivity === 1) && duration >= 60) {
          // Process apps only for productivity 0 and 1
          const appIndex = userObject.apps.findIndex((a) => a.name === friendlyAppName);
          if (appIndex === -1) {
            userObject.apps.push({ name: friendlyAppName, duration });
          } else {
            userObject.apps[appIndex].duration += duration;
          }
        }
      }
    }
  });

  // Sort apps and browser websites by duration, descending
  employeeData.forEach((userObject) => {
    userObject.apps.sort((a, b) => b.duration - a.duration);
    if (userObject.browserWebsites.length > 0) {
      userObject.browserWebsites.sort((a, b) => b.duration - a.duration);
    }
  });

  return employeeData;
}
5

The tool you used formatted your code and also reduced your indentation whitespace from 4 to 2 characters.

There are definitely changes, if you run the before and after through a text compare/diff tool.

Screenshot_2024-05-10_180515
Screenshot_2024-05-10_180515878×706 39.6 KB

1 Like