skip to Main Content

It was working fine up until yesterday, its a script to take in one number and see how many combinations another set of numbers will add up to that one number. It is producing a blank field in column J, where it has been showing what combinations will make up the top number.

function findAndDisplayMaxCombination() {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var targetAmount = sheet.getRange("G2").getValue();
    var values = sheet.getRange("G4:G" + sheet.getLastRow()).getValues().flat();

    // Set the time limit to 3 minutes (in seconds)
    var timeLimit = 600;
    var startTime = new Date().getTime();

    // Find the combination with the most values
    var maxCombination = findMaxCombination(targetAmount, values, startTime, timeLimit);

    // Ensure the script has permission to edit the sheet
    SpreadsheetApp.getActiveSpreadsheet().toast("Authorization check passed. Updating the sheet...", "Status", 5);

    // Clear the contents of column I
    sheet.getRange("I4:I").clearContent();

    // Write each value in the max combination to column J
    for (var i = 0; i < maxCombination.length; i++) {
      sheet.getRange("I" + (i + 4)).setValue(maxCombination[i]);
    }
  } catch (error) {
    // Log any errors
    Logger.log("Error: " + error);
    SpreadsheetApp.getActiveSpreadsheet().toast("An error occurred. Check the logs for details.", "Error", 5);
  }
}

function findMaxCombination(target, values, startTime, timeLimit) {
  var result = [];

  function search(index, currentSum, currentCombination) {
    // Check if the time limit has been exceeded
    if (new Date().getTime() - startTime > timeLimit * 1000) {
      return;
    }

    if (currentSum === target) {
      if (currentCombination.length > result.length) {
        result = currentCombination.slice(); // Update result with the longer combination
      }
      return;
    }

    if (index === values.length) {
      return;
    }

    search(index + 1, currentSum, currentCombination); // Exclude the current value
    search(index + 1, currentSum + values[index], currentCombination.concat(values[index])); // Include the current value
  }

  search(0, 0, []);
  return result;
}

2

Answers


  1. Chosen as BEST ANSWER

    This doesn't produce any errors but it still is not producing an output:

    function findAndDisplayMaxCombination() {
      try {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var targetAmount = sheet.getRange("G2").getValue();
        var values = sheet.getRange("G4:G" + sheet.getLastRow()).getValues().flat();
        var timeLimit = 600;
        var startTime = new Date().getTime();
        var maxCombination = findMaxCombination(targetAmount, values, startTime, timeLimit);
        SpreadsheetApp.getActiveSpreadsheet().toast("Authorization check passed. Updating the sheet...", "Status", 5);
        sheet.getRange("I4:I").clearContent();
        for (var i = 0; i < maxCombination.length; i++) {
          sheet.getRange("I" + (i + 4)).setValue(maxCombination[i]);
        }
      } catch (error) {
        Logger.log("Error: " + error);
        SpreadsheetApp.getActiveSpreadsheet().toast("An error occurred. Check the logs for details.", "Error", 5);
      }
    }
    
    function search(index, currentSum, currentCombination) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var startTime = new Date().getTime();
      var timeLimit = 600;
      var target = sheet.getRange("G2").getValue();
      var values = sheet.getRange("G4:G" + sheet.getLastRow()).getValues().flat();
      if (new Date().getTime() - startTime > timeLimit * 1000) {
        return;
      }
      if (currentSum === target) {
        if (currentCombination.length > result.length) {
          result = currentCombination.slice(); // Update result with the longer combination
        }
        return;
      }
      if (index === values.length) {
        return;
      }
      search(index + 1, currentSum, currentCombination); // Exclude the current value
      search(index + 1, currentSum + values[index], currentCombination.concat(values[index])); // Include the current value
    }
    
    function findMaxCombination(target, values, startTime, timeLimit) {
      var result = [];
     search(0, 0, []);
      return result;
    
    }
    

  2. You need to rewrite you code like this:

    function findAndDisplayMaxCombination() {
      try {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var targetAmount = sheet.getRange("G2").getValue();
        var values = sheet.getRange("G4:G" + sheet.getLastRow()).getValues().flat();
        var timeLimit = 600;
        var startTime = new Date().getTime();
        var maxCombination = findMaxCombination(targetAmount, values, startTime, timeLimit);
        SpreadsheetApp.getActiveSpreadsheet().toast("Authorization check passed. Updating the sheet...", "Status", 5);
        sheet.getRange("I4:I").clearContent();
        for (var i = 0; i < maxCombination.length; i++) {
          sheet.getRange("I" + (i + 4)).setValue(maxCombination[i]);
        }
      } catch (error) {
        Logger.log("Error: " + error);
        SpreadsheetApp.getActiveSpreadsheet().toast("An error occurred. Check the logs for details.", "Error", 5);
      }
    }
    
    function search(index, currentSum, currentCombination) {
      if (new Date().getTime() - startTime > timeLimit * 1000) {
        return;
      }
      if (currentSum === target) {
        if (currentCombination.length > result.length) {
          result = currentCombination.slice(); // Update result with the longer combination
        }
        return;
      }
      if (index === values.length) {
        return;
      }
      search(index + 1, currentSum, currentCombination); // Exclude the current value
      search(index + 1, currentSum + values[index], currentCombination.concat(values[index])); // Include the current value
    }
    
    function findMaxCombination(target, values, startTime, timeLimit) {
      var result = [];
     search(0, 0, []);
      return result;
    }
    

    And then rerun it and you start getting the error that startTime is undefined. Presumably you can debug it from there.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search