Created
February 19, 2025 03:54
-
-
Save annakrasner/04ea576489ef1f56c9e6c0df48059e4c to your computer and use it in GitHub Desktop.
Google Apps Script as an example to intake data from several Google Forms linked to one Google Sheet and place new rows at the top
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function myFunction() { | |
| } | |
| //trigger this funciton via a Form Submit trigger | |
| function handleResponses(e) | |
| { | |
| var sourceSheet = e.range.getSheet(); | |
| var isScript = sourceSheet.getSheetId() == SpreadsheetApp.getActive().getSheetByName("Scripts").getSheetId(); | |
| var isEvent = sourceSheet.getSheetId() == SpreadsheetApp.getActive().getSheetByName("Events").getSheetId(); | |
| var isOther = sourceSheet.getSheetId() == SpreadsheetApp.getActive().getSheetByName("Other").getSheetId(); | |
| var timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone(); | |
| var copySheet = SpreadsheetApp.getActive().getSheetByName("To Do"); | |
| //add row on the top of todo sheet, paste cells from last row of scripts into it | |
| var sourceLastRow = sourceSheet.getLastRow(); | |
| copySheet.insertRowBefore(2); | |
| //timestamp | |
| copySheet.getRange(2,2).setValue(sourceSheet.getRange(sourceLastRow,1).getValue()); | |
| //headline handle | |
| copySheet.getRange(2,4).setValue(sourceSheet.getRange(sourceLastRow,3).getValue()); | |
| //link or rsvp | |
| copySheet.getRange(2,5).setValue(sourceSheet.getRange(sourceLastRow,4).getValue()); | |
| var submissionType = "UNKNOWN"; | |
| if(isScript) | |
| { | |
| submissionType = "SCRIPT"; | |
| //representatives | |
| copySheet.getRange(2,6).setValue(sourceSheet.getRange(sourceLastRow,5).getValue()); | |
| //deadline | |
| copySheet.getRange(2,11).setValue(sourceSheet.getRange(sourceLastRow,6).getValue()); | |
| } | |
| else if(isEvent) | |
| { | |
| submissionType = "EVENT"; | |
| //event detail | |
| copySheet.getRange(2,9).setValue(sourceSheet.getRange(sourceLastRow,5).getValue()); | |
| //date time | |
| var date = sourceSheet.getRange(sourceLastRow, 6).getValue(); | |
| var dLabel = Utilities.formatDate(date,SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "E M/dd"); | |
| var startTime = sourceSheet.getRange(sourceLastRow, 7).getValue(); | |
| var stime = Utilities.formatDate(startTime, timezone, "h:mm a"); | |
| //end time of event is optional, so check if cell is blank before using as formatdate arg | |
| var endTime = sourceSheet.getRange(sourceLastRow, 8).getValue(); | |
| var eTime = ""; | |
| if(endTime != "") | |
| { | |
| eTime = Utilities.formatDate(endTime, timezone, "h:mm a"); | |
| } | |
| copySheet.getRange(2,7).setValue(dLabel + " " + stime + "-" + eTime); | |
| //location | |
| copySheet.getRange(2,8).setValue(sourceSheet.getRange(sourceLastRow,9).getValue()); | |
| //resource | |
| copySheet.getRange(2,10).setValue(sourceSheet.getRange(sourceLastRow,10).getValue()); | |
| } | |
| else if(isOther) | |
| { | |
| submissionType = "NEWS_BLOG"; | |
| //resource | |
| copySheet.getRange(2,10).setValue(sourceSheet.getRange(sourceLastRow,5).getValue()); | |
| } | |
| //slack handle | |
| var handle = sourceSheet.getRange(sourceLastRow, 2).getValue(); | |
| var label = submissionType + ": " + handle; | |
| copySheet.getRange(2,3).setValue(label); | |
| } |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If you have a set of Google Forms feeding into sheets in one Spreadsheet, you can make a script like this by using Extensions/Apps Script and copy the cells as needed.
Tip: using setValue()/getValue() instead of copyTo() prevents conditional formatting in target sheet from being disrupted.