View original (French)

Copy and paste a line based on condition

1Zandon Posted messages 4 Status Member -  
1Zandon Posted messages 4 Status Member -

Hello,

I have a Google Sheets document that corresponds to a list of participants.

I would like that when a row is yellow, that same row is copied and pasted into a second tab.

Is that possible?

Thank you

4 answers

  1.
    Bruno83200_6929 Posted messages 725 Registration date   Status Member Last intervention   170
     

    Yes, it is possible to do this by using Google Apps Script, which is a scripting language based on JavaScript to automate tasks in Google Sheets. Here is an example of code that could accomplish what you are asking for:

    You need to use this script in JavaScript:


    function copyYellowRows() {
      var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SourceSheet'); // Replace 'SourceSheet' with the name of your source sheet
      var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DestinationSheet'); // Replace 'DestinationSheet' with the name of your destination sheet
      var sourceRange = sourceSheet.getDataRange();
      var sourceValues = sourceRange.getValues();
      var destinationValues = [];
      
      for (var i = 0; i < sourceValues.length; i++) {
        var color = sourceRange.getCell(i + 1, 1).getBackground(); // Checks the color of the first cell in each row
        if (color == "#ffff00") { // If the color is yellow (hex code for yellow)
          destinationValues.push(sourceValues[i]);
        }
      }
      
      if (destinationValues.length > 0) {
        destinationSheet.getRange(1, 1, destinationValues.length, destinationValues[0].length).setValues(destinationValues);
      }
    }

    To use this script:

    In your Google Sheets, go to "Extensions" > "Apps Script".
    Paste the code into the script editor.
    Save the script and give it a name.
    You can now run the script by clicking on the play ▶️ icon.
    Make sure to replace "SourceSheet" and "DestinationSheet" with the names of your sheets. This script will copy all rows with a yellow background color from the source sheet to the destination sheet. Also, ensure that the yellow you are using is indeed the hexadecimal code #ffff00. If your yellow is slightly different, you will need to adjust the code accordingly.

    There you go. It should work logically. Good luck.

    0
  2. 1Zandon Posted messages 4 Status Member
     

    Thank you, it's awesome, it works!!!!

    And can it be automatic?

    I notice that I have to restart the script every time I put a line in yellow?

    Thank you

    0
    1. Bruno83200_6929 Posted messages 725 Registration date   Status Member Last intervention   170
       

      Hello again,

      I'm glad to have helped you. To make this script automatic, you can use triggers in Google Apps Script. Triggers allow you to run script functions at specific times or on a regular basis. Here's how you can do it:

      - Open your Google Sheets where the script is located.
      - In the top menu, click on "Script editor" to open the Google Apps Script editor.
      - Paste the script into the editor.
      - Then, click on the clock icon at the top to access the "Triggers".
      - Click on "Add Trigger" at the bottom right.
      - Select the function to execute ("copyYellowRows") from the dropdown menu.
      - Choose how you want to trigger the script (for example, "Run" > "Time-driven" > "Day timer").
      - Configure the execution frequency details.
      - Click "Save".


      Now, your script will be automatically executed according to the trigger configuration you set. Make sure the script has the necessary permissions to access the spreadsheets and modify the data.

      Logically, it should work.

      See you soon.

      0
  3. 1Zandon Posted messages 4 Status Member
     

    Thank you for taking the time ????

    0
  4. 1Zandon Posted messages 4 Status Member
     

    Thank you for taking the time to respond

    My emoji ???? has turned into question marks ;)

    0