CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsautomationgoogle-forms
Jarrett Stone
Jarrett Stone

Reputation: 3

Autofilling info from Google Forms to Sheets

I'm a lawyer who is completely out of his element here. I've been researching and running tests on Google Script, however, I keep running into issues.

The goal:

Step 1 - Someone completes a Google Form (info includes name, number, etc.);

Step 2 - A NEW template spreadsheet is automatically generated in Google Drive;

Step 3 - The info from the Form is automatically populated within the new template spreadsheet.

Can anyone be a life saver and help me out? Or at least put me out of my misery haha.

Thanks so much in advance!

Update #1:

Based on a comment, here's where I'm at. Good news is that it accomplishes Step 1 and Step 2. Bad news is that I'm still figuring out Step 3, which is probably because I'm so new to this.

Anyways, here's what I have for Step 1 and 2:

function autoFillCaseInfofromIntake(e) {
  var Timestamp = e.values[0];
  var Source = e.values[1];
  var FullName = e.values[2];
  
  var templatefile = DriveApp.getFileById("I inserted ID here");

  var templatepopulatedfolder = DriveApp.getFolderById("I inserted ID here");
  
  var copy = templatefile.makeCopy(FullName, templatepopulatedfolder);
  
  var sheet = DocumentApp.openById(copy.getId());
 
  sheet.saveAndClose();

Again, sorry for the ignorance!

Update #2:

Big thanks to @arul selvan and @Iamblichus for helping to get me to where I needed to go!

Just in case someone needs the answer in the future:

function autoFillCaseInfofromIntake(e) {
  var Timestamp = e.values[0];
  var Source = e.values[1];
  var FullName = e.values[2];
  
  var templatefile = DriveApp.getFileById("I inserted ID here");

  var templatepopulatedfolder = DriveApp.getFolderById("I inserted ID here");
  
  var copy = templatefile.makeCopy(FullName, templatepopulatedfolder);
  
  var newsheet = SpreasheetApp.openById(copy.getId());
  var sht1 = newsheet.getSheetByName("Sheet1");//open the default sheet 1 or if you want you can rename the sheet or create a new sheet
  sht1.getRange("A1").setValue(Source); //populate any cell with value captured by the form
  sht1.getRange("B1").setValue(FullName);

}

Upvotes: 0

Views: 96

Answers (1)

arul selvan
arul selvan

Reputation: 624

You cracked the difficult steps 1 and 2. The Step 3 is easy.

function autoFillCaseInfofromIntake(e) {
  var Timestamp = e.values[0];
  var Source = e.values[1];
  var FullName = e.values[2];
  
  var templatefile = DriveApp.getFileById("I inserted ID here");

  var templatepopulatedfolder = DriveApp.getFolderById("I inserted ID here");
  
  var copy = templatefile.makeCopy(FullName, templatepopulatedfolder);
  
  var newsheet = SpreadsheetApp.openById(copy.getId());
  var sht1 = newsheet.getSheetByName("Sheet1");//open the default sheet 1 or if you want you can rename the sheet or create a new sheet
  sht1.getRange("A1").setValue(Source); //populate any cell with value captured by the form
  sht1.getRange("B1").setValue(FullName);

}

Explanation:

I'm sure you figured out how to run the above script "On formSubmit"

Upvotes: 1

Related Questions