How to remove all short code (almost similar) from a column on Google sheet

Question

I have a Google sheet array with a column of 63 rows (column C) with short codes,

[audio mp3:="https/...." ][/audio]

Each line has this tag but with a different link. but always with this
[audio mp3:="https/...." ][/audio]

C1: [mp3 audio:="https/upload/content/file1.mp3"][/audio]  
C2: [mp3 audio:="https/upload/content/file2.mp3"][/audio]

How do I find and delete all terms that start with " [mp3 audio" and end with "[/audio]" on all 63 rows in column C`?

It looks like you are looking to create a regex, but do not know where to get started. Please check [Reference - What does this regex mean](https://stackoverflow.com/questions/22937618) resource, it has plenty of hints. Also, refer to [Learning Regular Expressions](https://stackoverflow.com/questions/4736) post for some basic regex info. Once you get some expression ready and still have issues with the solution, please edit the question with the latest details and we'll be glad to help you fix the problem. — Wiktor Stribiżew, Nov 19 '20 at 09:16

iansedano · Answer 1 · 2020-11-19T09:43:46.993

A sample script using REGEX

function deleteTerms() {
  // REGEX pattern
  let pattern = /mp3 audio\:="http.+"]\[\/audio]/;

  // Initialize Spreadsheet, Sheet, Range, Values
  let ss = SpreadsheetApp.getActive();
  let sh = ss.getSheetByName("Sheet1"); // Replace with sheet name.
  let range = sh.getDataRange();
  let values = range.getValues(); // Returns 2D array

  // Temporary store of values to delete
  let indicesToDelete = [];

  // Go through 2D array to find matches to pattern
  // and add to store of values to delete
  values.forEach((row, rowIndex) => {
    row.forEach((cell, cellIndex) => {
      if (pattern.exec(cell) != null) {
        indicesToDelete.push([rowIndex, cellIndex]);
      }
    });
  });

  // Go through store of values, and replace them with ""
  indicesToDelete.forEach((item) => {
    values[item[0]][item[1]] = "";
  });

  // Write values to sheet
  range = range.offset(0,0, values.length, values[0].length)
  range.setValues(values)
}

Make sure the Sheet name is right before you run it.
You can adapt this script to do what you like once you find the results. Whether that is to delete the column, to move the values elsewhere, or just delete the value, as I have done.
This script will find the value whether its in column C or anywhere in the sheet.

How to remove all short code (almost similar) from a column on Google sheet

1 Answers1

A sample script using REGEX

References