Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

in Google sheets, is there any way for a cell (for example cell B1) to display certain…

  1. User

    in Google sheets, is there any way for a cell (for example cell B1) to display certain text when cell A1 contains certain word that matches pre-defined text?

  2. Assistant

    Yes, this can be achieved using the IF function in Google Sheets.

    Here is an example formula:

    =IF(A1="Apple", "Fruit", "")
    

    In this formula, if cell A1 contains the word "Apple", then cell B1 will display the text "Fruit". If cell A1 does not contain the word "Apple", then cell B1 will be blank.

    You can modify this formula to match your exact requirements.

  3. User

    can you do that without using a formula?

  4. Assistant

    No, it is not possible to achieve this without using a formula. Formulas are the only way to dynamically link one cell's content to another in Google Sheets. If you want the text in a cell to change based on the value in another cell, you will need to use a formula.

  5. User

    what about using GAS (google app script)

  6. Assistant

    Yes, you can achieve this using Google Apps Script (GAS).

    Here is an example GAS code:

    function onEdit(e) {
      var sheet = e.range.getSheet();
      var column = e.range.getColumn();
      var row = e.range.getRow();
      
      if (column == 1 && row == 1 && e.value == "Apple") {
        sheet.getRange(row, 2).setValue("Fruit");
      } else {
        sheet.getRange(row, 2).setValue("");
      }
    }
    

    This code will run every time a cell in the sheet is edited, and it checks if the edited cell is in column 1 and row 1 and contains the word "Apple". If so, it will set the value of the cell in the corresponding row and column 2 to "Fruit". If the edited cell does not contain the word "Apple", it will clear the value of the corresponding cell in column 2.

    You will need to save this code as a Google Apps Script in your Google Sheets file. To do this, go to "Tools" > "Script editor" and paste the code into the script editor. Once you have saved the script, it will run automatically whenever a cell is edited in the sheet.

  7. User

    modify the above script, so that instead of checking only cell A1, it will check the data every time a new data is inserted in the following cells below it. (A2, A3, A4.. so on)

  8. Assistant

    Sure! Here's the modified code that checks for changes in column A and updates the corresponding cell in column B:

    function onEdit(e) {
      var sheet = e.range.getSheet();
      var column = e.range.getColumn();
      var row = e.range.getRow();
      var numRows = sheet.getLastRow();
    
      // Check if the edited cell is in column A and within the max number of rows in the sheet
      if (column === 1 && row <= numRows) {
        var value = sheet.getRange(row, column).getValue();
        var output = '';
    
        // Check if value contains word "Apple" and set output accordingly
        if (value === 'Apple') {
          output = 'Fruit';
        }
    
        // Update corresponding cell in column B
        sheet.getRange(row, 2).setValue(output);
      }
    }
    

    This code checks if the edited cell is in column A and within the maximum number of rows in the sheet. If it is, the script gets the value of the edited cell and checks if it contains the word "Apple". If it does, it sets the output to "Fruit". Finally, the script updates the corresponding cell in column B with the output.

    This script should update cell B2 when A2 is edited, cell B3 when A3 is edited, and so on for all rows in column A.

  9. User

    does this work even when value partially contains "apple"? Like "www.apple.com" or "apple cider"?

  10. Assistant

    Good question! The current script will only set the output to "Fruit" if the cell in column A exactly matches the word "Apple". If you want the output to be set to "Fruit" even if the word "Apple" is a part of the value in column A, you can modify the code to use a regular expression to check if the word "Apple" is present in the value. Here's the modified code:

    function onEdit(e) {
      var sheet = e.range.getSheet();
      var column = e.range.getColumn();
      var row = e.range.getRow();
      var numRows = sheet.getLastRow();
      
      // Check if the edited cell is in column A and within the max number of rows in the sheet
      if (column === 1 && row <= numRows) {
        var value = sheet.getRange(row, column).getValue();
        var output = '';
        
        // Use regular expression to check if the word "Apple" is present in the value
        if (/apple/i.test(value)) {
          output = 'Fruit';
        }
        
        // Update corresponding cell in column B
        sheet.getRange(row, 2).setValue(output);
      }
    }
    

    This modified code uses a regular expression (/apple/i.test(value)) to check if the word "Apple" (case insensitive) is present in the value. If it is, the output is set to "Fruit". Finally, the script updates the corresponding cell in column B with the output.

    This script should update cell B2 when A2 is edited, cell B3 when A3 is edited, and so on for all rows in column A, even if the value in column A contains the word "Apple" as a part of some other text.

  11. User

    modify the above so that it checks the data in columns E (instead of A), and the output is columns L

  12. Assistant

    Sure, here's the modified code that checks for changes in column E and updates the corresponding cell in column L:

    function onEdit(e) {
      var sheet = e.range.getSheet();
      var column = e.range.getColumn();
      var row = e.range.getRow();
      var numRows = sheet.getLastRow();
    
      // Check if the edited cell is in column E and within the max number of rows in the sheet
      if (column === 5 && row <= numRows) {
        var value = sheet.getRange(row, column).getValue();
        var output = '';
    
        // Use regular expression to check if the word "Apple" is present in the value
        if (/apple/i.test(value)) {
          output = 'Fruit';
        }
        
        // Update corresponding cell in column L
        sheet.getRange(row, 12).setValue(output);
      }
    }
    

    This modified code checks if the edited cell is in column E and within the maximum number of rows in the sheet. If it is, the script gets the value of the edited cell and checks if it contains the word "Apple". If it does, it sets the output to "Fruit". Finally, the script updates the corresponding cell in column L with the output.

    This script should update cell L2 when E2 is edited, cell L3 when E3 is edited, and so on for all rows in column E.

  13. User

    modify the above so that it checks if "Apple", "mercari", "auctions" are present in the value. And outputs "Fruit", "メルカリ", "ヤフオク", respectively

  14. Assistant

    Here's the modified code that checks if "Apple", "mercari", "auctions" are present in the value and outputs "Fruit", "メルカリ", "ヤフオク", respectively:

    function onEdit(e) {
      var sheet = e.range.getSheet();
      var column = e.range.getColumn();
      var row = e.range.getRow();
      var numRows = sheet.getLastRow();
    
      // Check if the edited cell is in column E and within the max number of rows in the sheet
      if (column === 5 && row <= numRows) {
        var value = sheet.getRange(row, column).getValue();
        var output = '';
    
        // Check if the word "Apple" is present in the value and set output to "Fruit" if it is
        if (/apple/i.test(value)) {
          output = 'Fruit';
        }
    
        // Check if the word "mercari" is present in the value and set output to "メルカリ" if it is
        if (/mercari/i.test(value)) {
          output = 'メルカリ';
        }
    
        // Check if the word "auctions" is present in the value and set output to "ヤフオク" if it is
        if (/auctions/i.test(value)) {
          output = 'ヤフオク';
        }
        
        // Update corresponding cell in column L
        sheet.getRange(row, 12).setValue(output);
      }
    }
    

    This modified code checks if the edited cell is in column E and within the maximum number of rows in the sheet. If it is, the script gets the value of the edited cell and checks if it contains any of the words "Apple", "mercari", "auctions". If it does, it sets the output to "Fruit", "メルカリ", "ヤフオク", respectively. Finally, the script updates the corresponding cell in column L with the output.

    This script should update cell L2 when E2 is edited, cell L3 when E3 is edited, and so on for all rows in column E.