I'm looking for a script for google sheets that does the following thing. I want it to…
- User
I'm looking for a script for google sheets that does the following thing. I want it to look at each value in columns C, H, M, and R in the active sheet. If that value has a a richtext hyperlink in it, I want the script to search for all the values in column C of another sheet called "List" and look for a match for that hyperlink URL. If a match exists (that is, if the address of the richtext hyperlink in the active sheet matches a value of a cell in the C column of sheet "List"), I want the script to move to the next cell. If there is no match, I want the script copy the URL of the link from the richtext to an empty cell in the C column in List. The row of that cell should be one row below the lowest row in "List" where the C-column cell of that row is occupied. Can you please try to compose that script?
- Assistant
Here's a script that performs the actions you described:
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); } } } }); }To use this script in your Google Sheet, follow these steps:
- Open your Google Sheet.
- Click on "Extensions" > "Apps Script."
- Remove any existing code in the "Code.gs" file, if necessary.
- Paste the provided script into the "Code.gs" file.
- Save the script by clicking on the floppy disk icon in the top-left corner or by pressing
Ctrl+S. - In the script editor, click on the "Select function" dropdown and choose
processHyperlinks. - Click on the play button (a triangular "Run" icon) to run the script.
- The script will now process your hyperlinks based on your description.
After running the script, you may need to provide permission for the script to access your Google Sheet.
- User
That worked great! Now I want to add more functionality to the script. Whenever a new entry is made in the C column of sheet "List", I want the value of the E column have the value that's the result of the IMPORTRANGE function from the URL in the C column, from the D1 cell of the sheet named "<>". So if a new entry was made in cell C206, I would want the cell E206 have the following formula: =IMPORTRANGE(C206,"<>!D1"). Next, the B column of the same row should display the first two words of the value of the E column. So on row C206 I would want the script to place the following function into the B column: =IFERROR(regexextract(E206,"[\w]* [\w]*"),""). Please adjust the script you made to include this extra functionality.
- Assistant
Here's the updated script that includes the additional functionality you requested:
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); } } } }); }This updated script will set the
IMPORTRANGEformula in the E column and theregexextractformula in the B column whenever a new entry is made in the C column of the "List" sheet based on your description.You can add this updated script to your Google Sheet following the same steps I previously provided.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.