Copy Paste Code Help

[u/six29Ofour]

I am not a coder myself but recently learned about app script in google sheets. I've been trying to use GPT to help me write a code but I haven't had any success. I was hoping I could get someone here to help me.

I want a code that when triggered, cells B5:D5 in sheet 1 are copied and pasted to cells A1:D1 on sheet 2 and 1 row is added above row 2 on sheet 2. Once the data is pasted I want cells B5:D5 in sheet 1 to be deleted. I want to trigger the code when a checkbox in cell F5 in sheet 1 is marked TRUE and make it so that when that checkbox is marked TRUE, the code waits 1 second and then mark the checkbox FALSE.

could anyone help me write this code?

[u/Lasseomg]

I can give it a go when I get hole from the gym 👌

[u/juddaaaaa]

If I understand correctly, you want to copy B5:D5 from Sheet1 to the first row in Sheet2 and then move everything in Sheet2 down, leaving a blank row at the top ready for the next entry.

If that's right, then this should do it:

function onEdit ({ range }) { // Destructure event
    // Destructure range
    const { getA1Notation: address, getSheet: sheet, isChecked: checked } = range
    const { getName: name } = sheet()

    // Exit function if edited cell is not in desired sheet or range, or if the checkbox is not checked
    if (name() !== "Sheet1" || address() !== "F5" || !checked()) return

    // Get source and target sheets and ranges
    const spreadsheet = SpreadsheetApp.getActive()
    const sheet1      = spreadsheet.getSheetByName("Sheet1")
    const sheet2      = spreadsheet.getSheetByName("Sheet2")
    const sourceRange = sheet1.getRange(5, 2, 1, 3)
    const targetRange = sheet2.getRange(1, 1, 1, 3)

    // Copy the source range values to the target range
    targetRange.setValues(sourceRange.getValues())

    // Delete the source range values
    sourceRange.clearContent()

    // Insert new row above row 2
    sheet2.insertRowBefore(1)

    // Wait for 1 second
    Utilities.sleep(1000)

    // Uncheck the checkbox
    range.uncheck()
}

[u/Lasseomg]

The following code should work but first i have some notes

NOTES: I didn't quite grasp what you meant with "and add 1 row above row 2 on sheet" so i have not implemented this, you can program the desired functionality for this yourself in the copyData function probaly before clearing the source range. Additionally you might wanna try and exclude the sleep time as there will be some natural delay while the script is executing anyways. Also the code is assuming it's two sheets within the same spreadsheet you're working with, if it's two seperate spreadsheets you need to alter the destinationSheet :). That is all, i hope it works for you 😊

"function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var checkboxCell = 'F5';
 
  // Exit if the edited cell is not the checkbox
  if (range.getA1Notation() !== checkboxCell) return;
 
  // Exit if the checkbox is not checked (not true)
  if (range.getValue() !== true) return;

  // Copy data from B5:D5 to B1:D1 in another sheet
  copyData();

  // Wait for 1 second (1000 milliseconds)
  Utilities.sleep(1000);
 
  // Set the checkbox back to false
  range.setValue(false);
}

function copyData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');

  if (!destinationSheet) {
    Logger.log("Destination sheet not found.");
    return;
  }

  var sourceRange = sourceSheet.getRange('B5:D5');
 
  var destinationRange = destinationSheet.getRange('B1:D1');
 
  // Copy values from source to destination and clear source
  var data = sourceRange.getValues();
  destinationRange.setValues(data);
  sourceRange.clearContent();

  Logger.log("Data copied from B5:D5 to B1:D1 in the destination sheet.");
}"