Here is a function that works, but I want to add further functionality to it. I want to…
- 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); } } }}); }
- 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.
- User
That gave me this error: Error ReferenceError: newECellValue is not defined (anonymous) @ Code.gs:73 processHyperlinks @ Code.gs:44
- 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);tolistSheet.getRange("E" + newRow).setFormula(importRangeFormula);and added a new line below it to define thenewECellValuevariable.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 theregexextractfunction to the B column as before.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.