Skip to content

Instantly share code, notes, and snippets.

@annakrasner
Created February 19, 2025 03:54
Show Gist options
  • Select an option

  • Save annakrasner/04ea576489ef1f56c9e6c0df48059e4c to your computer and use it in GitHub Desktop.

Select an option

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
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);
}
@annakrasner
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment