I have a Google Sheet where all the users who play the wheel of fortune are copied. In column E, for example, these values ​​are copied from the database: a:1:{i:0;s:12:"Half Price";}

I would like the script to extract only the prize won "Half Price" in column H, on the same line. If no win is made (as in E7) I can write NO WIN

I attach an image for clarity

Try these Approaches

1. Using Apps Script

For this part loop through each row to extract the prize text or "NO WIN"

const results = data.map(row => {
        const prizeData = row[0];
        if (prizeData === 'a:0:{}') {
          return ['NO WIN'];
        }

and for this one Regex to extract the prize text from the pattern a:1:{i:0;s:12:"Metà Prezzo";}

    const match = prizeData.match(/s:\d+:\"([^\"]+)\"/);
    const prize = match ? match[1] : null;

Script used

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange(2, 5, sheet.getLastRow() - 1);
  const prizeColumn = sheet.getRange(2, 8, sheet.getLastRow() - 1);
  const data = dataRange.getValues();

  const results = data.map(row => {
    const prizeData = row[0];
    if (prizeData === 'a:0:{}') {
      return ['NO WIN'];
    }

    const match = prizeData.match(/s:\d+:\"([^\"]+)\"/);
    const prize = match ? match[1] : null;

    if (prize === "Metà Prezzo" || prize === "NO WIN") {
      return [prize];
    } else {
      return [''];
    }
  });

  prizeColumn.setValues(results);
}

Sample Output

2. Using Google sheet Formula

The BYROW function in combination with LAMBDA, IF, and FIND to process each value in a range of cells from E2 to E8, check for specific conditions, and return corresponding values based on those conditions.

=BYROW(E2:E8, LAMBDA(r, IF(r = "a:0:{}", "No Win", IFERROR(IF(FIND("Metà Prezzo",r)>0,r,""),""))))

References

• Syntax for Regular Expressions
• BYROW
• FIND