Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

Here is a function that works, but I want to add further functionality to it. I want to…

  1. User

    Here is a function that works, but I want to add further functionality to it. I want to add to the if (!isFound) { branch the following extra steps: If the newly created value in the E column sheet "List" is an error, the script continue as it is now. However, if the newly created value in the E column sheet "List" is not an error, I want the script to clear all the values that it had just placed (in column B, C and E on the new row of sheet "List") and then continue. Here is the original script:

    function processHyperlinks() { var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List"); var columnsToCheck = ["C", "H", "M", "R"];

    columnsToCheck.forEach(function (column) { var numRows = activeSheet.getLastRow(); var range = activeSheet.getRange(column + "1:" + column + numRows); var values = range.getRichTextValues();

    for (var i = 0; i < values.length; i++) {
      var richText = values[i][0];
      if (richText.getLinkUrl()) {
        var url = richText.getLinkUrl();
        var listRange = listSheet.getRange("C1:C" + listSheet.getLastRow());
        var listValues = listRange.getValues();
        var isFound = false;
    
        for (var j = 0; j < listValues.length; j++) {
          if (listValues[j][0] == url) {
            isFound = true;
            break;
          }
        }
    
        if (!isFound) {
          var newRow = listSheet.getLastRow() + 1;
          listSheet.getRange("C" + newRow).setValue(url);
    
          // Add IMPORTRANGE function to E column
          var importRangeFormula = '=IMPORTRANGE(C' + newRow + ',"<<Binder>>!D1")';
          listSheet.getRange("E" + newRow).setFormula(importRangeFormula);
    
          // Add regexextract function to B column
          var regexExtractFormula = '=IFERROR(REGEXEXTRACT(E' + newRow + ',"[\\w]* [\\w]*"),"")';
          listSheet.getRange("B" + newRow).setFormula(regexExtractFormula);
    
        }
      }
    }
    

    }); }

  2. Assistant

    Here's the modified script with the additional functionality added to the if (!isFound) branch:

    function processHyperlinks() {
      var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
      var columnsToCheck = ["C", "H", "M", "R"];
    
      columnsToCheck.forEach(function (column) {
        var numRows = activeSheet.getLastRow();
        var range = activeSheet.getRange(column + "1:" + column + numRows);
        var values = range.getRichTextValues();
    
        for (var i = 0; i < values.length; i++) {
          var richText = values[i][0];
          if (richText.getLinkUrl()) {
            var url = richText.getLinkUrl();
            var listRange = listSheet.getRange("C1:C" + listSheet.getLastRow());
            var listValues = listRange.getValues();
            var isFound = false;
    
            for (var j = 0; j < listValues.length; j++) {
              if (listValues[j][0] == url) {
                isFound = true;
                break;
              }
            }
    
            if (!isFound) {
              var newRow = listSheet.getLastRow() + 1;
              listSheet.getRange("C" + newRow).setValue(url);
    
              // Add IMPORTRANGE function to E column
              var importRangeFormula = '=IMPORTRANGE(C' + newRow + ',"<<Binder>>!D1")';
              var newECellValue = listSheet.getRange("E" + newRow).setFormula(importRangeFormula);
              
              // Check if the new E column value is not an error
              if (!newECellValue.isError()) {
                // Clear cell values in B, C and E
                listSheet.getRange("B" + newRow + ":C" + newRow).clearContent(); 
                listSheet.getRange("E" + newRow).clearContent();
              } else {
                // Add regexextract function to B column
                var regexExtractFormula = '=IFERROR(REGEXEXTRACT(E' + newRow + ',"[\\w]* [\\w]*"), "")';
                listSheet.getRange("B" + newRow).setFormula(regexExtractFormula);
              }
            }
          }
        }
      });
    }
    

    This script will first do the same as before, and then it will check if the value in the E column is not an error. If it's not an error, it clears the cell values in columns B, C, and E for that row. If it's an error, it continues with adding the regexextract function to the B column as before.

  3. User

    That gave me this error: Error ReferenceError: newECellValue is not defined (anonymous) @ Code.gs:73 processHyperlinks @ Code.gs:44

  4. Assistant

    Apologies for the oversight. I made a mistake with the variable assignment. Please update the script as shown below:

    function processHyperlinks() {
      var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
      var columnsToCheck = ["C", "H", "M", "R"];
    
      columnsToCheck.forEach(function (column) {
        var numRows = activeSheet.getLastRow();
        var range = activeSheet.getRange(column + "1:" + column + numRows);
        var values = range.getRichTextValues();
    
        for (var i = 0; i < values.length; i++) {
          var richText = values[i][0];
          if (richText.getLinkUrl()) {
            var url = richText.getLinkUrl();
            var listRange = listSheet.getRange("C1:C" + listSheet.getLastRow());
            var listValues = listRange.getValues();
            var isFound = false;
    
            for (var j = 0; j < listValues.length; j++) {
              if (listValues[j][0] == url) {
                isFound = true;
                break;
              }
            }
    
            if (!isFound) {
              var newRow = listSheet.getLastRow() + 1;
              listSheet.getRange("C" + newRow).setValue(url);
    
              // Add IMPORTRANGE function to E column
              var importRangeFormula = '=IMPORTRANGE(C' + newRow + ',"<<Binder>>!D1")';
              listSheet.getRange("E" + newRow).setFormula(importRangeFormula);
              // Get the value of the new E cell
              var newECellValue = listSheet.getRange("E" + newRow).getValue();
              
              // Check if the new E column value is not an error
              if (newECellValue !== "#REF!" && newECellValue !== "#N/A") {
                // Clear cell values in B, C and E
                listSheet.getRange("B" + newRow + ":C" + newRow).clearContent(); 
                listSheet.getRange("E" + newRow).clearContent();
              } else {
                // Add regexextract function to B column
                var regexExtractFormula = '=IFERROR(REGEXEXTRACT(E' + newRow + ',"[\\w]* [\\w]*"), "")';
                listSheet.getRange("B" + newRow).setFormula(regexExtractFormula);
              }
            }
          }
        }
      });
    }
    

    I changed the line var newECellValue = listSheet.getRange("E" + newRow).setFormula(importRangeFormula); to listSheet.getRange("E" + newRow).setFormula(importRangeFormula); and added a new line below it to define the newECellValue variable.

    Now, the script is checking if the value in the E column is not an error using if (newECellValue !== "#REF!" && newECellValue !== "#N/A"). If it's not an error, it clears the cell values in columns B, C, and E for that row. If it's an error, it continues with adding the regexextract function to the B column as before.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.