Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets - Add a formula to the drop down button that automatically copies data to another sheet

I am looking for a script that I could input under the drop down button in a Google sheet.
The drop down has three options "not submitted", "in progress", and "submitted".

Most of the data on here makes use of a drawer button or form.

I just want to be able to copy the data of a row automatically to another sheet when I toggle the submitted in the drop down button in that row.

a glimpse of what the rows look like

Do you have any suggestions?

Edit
Basically from this sheet 1 to sheet 2 when submitted is toggled.

Sheet 1

to

Sheet 2

Solution

  • This can be done with regular formulas, in spreadsheet 2 you can set a formula like this =QUERY(IMPORTRANGE("{Link of sheet 1}","A:D"),"Select * WHERE (Col4='Submitted')")

    That way it will pull the data from the range of spreadsheet 1 only where the value of the column 4 in your range is set to "Submitted".

    But if you want to use Apps script you can use the OnEdit trigger so that it checks on every update and use the values.Batchget method to get the values of the range on sheet 1, then check in the script if the value of the position of 4 in the range is equal to "Submitted" then use the method values.update on spreadsheet 2

    But I would suggest using the formulas as its way easier and doesn't require any programming