We're sorry, a server error occurred. Please wait a bit and try again. (line 63, file "Code")

Question

Thank you in advance for helping.

I am trying to convert the most recent submitted data from Google Form/Google sheets to a "template" Google doc. Basically, when a user submit a form, it will convert the data from Google Sheet and create a new Google Doc.

Side note: Im not really a coder.. I found the base script online and tried to modified it accordingly. I would greatly appreciate a step by step if possible?

AGAIN, THANK YOU SO MUCH

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('SHEET-ID', 'A1:AU1');
var tactics = Sheets.Spreadsheets.Values.get('SHEET-ID', 'A2:AU2');
var templateId = 'DOCTEMPLATE-ID';
  
  for(var i = 0; i < tactics.values.length; i++){
    
    var Fclient = tactics.values[i][0];
    var Lclient = tactics.values[i][1];
    var birthday = tactics.values[i][2];
    var profession = tactics.values[i][3];
    var email = tactics.values[i][4];
    var phone = tactics.values[i][5];
    var whatsapp = tactics.values[i][6];
    var preferredcontact = tactics.values[i][7];
    var UScitizen = tactics.values[i][8];
    var Ocitizen = tactics.values[i][9];
    var Tsapre = tactics.values[i][10];
    var Pairplane = tactics.values[i][11];
    var Photelamen = tactics.values[i][12];
    var FFlyer = tactics.values[i][13];
    var hotelloy = tactics.values[i][14];
    var vistedcountries = tactics.values[i][15];
    var smoke = tactics.values[i][16];
    var allergies = tactics.values[i][17];
    var Othermed = tactics.values[i][18];
    var addANOTHER = tactics.values[i][19];
    var emergencyname = tactics.values[i][20];
    var emergencyphone = tactics.values[i][21];
    var emergencyrelation = tactics.values[i][22];
    var emergencyname2 = tactics.values[i][23];
    var emergencyphone2 = tactics.values[i][24];
    var emergencyrelation2 = tactics.values[i][25];
    var comptravelmag = tactics.values[i][26];
    var secondaryFname = tactics.values[i][27];
    var secondaryLname = tactics.values[i][28];
    var secondarybirthday = tactics.values[i][29];
    var secondaryprofession = tactics.values[i][30];
    var secondaryemail = tactics.values[i][31];
    var secondaryphone = tactics.values[i][32];
    var secondarywhatsapp = tactics.values[i][33];
    var secondarypreferredcontact = tactics.values[i][34];
    var secondaryUScitizen = tactics.values[i][35];
    var secondaryOcitizen = tactics.values[i][36];
    var secondaryTsapre = tactics.values[i][37];
    var secondaryPairplane = tactics.values[i][38];
    var secondaryPhotelamen = tactics.values[i][39];
    var secondaryFFlyer = tactics.values[i][40];
    var secondaryhotelloy = tactics.values[i][41];
    var secondaryvistedcountries = tactics.values[i][42];
    var secondarysmoke = tactics.values[i][43];
    var secondaryallergies = tactics.values[i][44];
    var secondaryOthermed = tactics.values[i][45];
       var timestamp = tactics.values[i][46];
    
    //Make a copy of the template file
    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
    
    //Rename the copied file
    DriveApp.getFileById(documentId).setName('Basic Information: ' + Lclient + 'test');
    
    //Get the document body as a variable. 
    var body = DocumentApp.openById(documentId).getBody(); **ERROR HERE**
    
    //Insert the supplier name
    body.replaceText('{{Fcilent}}', Fclient);
body.replaceText('{{Lcilent}}',	Lclient);
body.replaceText('{{birthday}}', birthday);
body.replaceText('{{profession}}', profession);
body.replaceText('{{email}}', email);
body.replaceText('{{phone}}', phone);
body.replaceText('{{whatsapp}}', whatsapp);
body.replaceText('{{preferredcontact}}', preferredcontact);
body.replaceText('{{UScitizen}}', UScitizen);
body.replaceText('{{Ocitizen}}', Ocitizen);
body.replaceText('{{Tsapre}}', Tsapre);
body.replaceText('{{Pairplane}}', Pairplane);
body.replaceText('{{Photelamen}}', Photelamen);
body.replaceText('{{FFlyer}}', FFlyer);
body.replaceText('{{hotelloy}}', hotelloy);
body.replaceText('{{vistedcountries}}', vistedcountries);
body.replaceText('{{smoke}}', smoke);
body.replaceText('{{allergies}}', allergies);
body.replaceText('{{Othermed}}', Othermed);
body.replaceText('{{addANOTHER}}', addANOTHER);
body.replaceText('{{emergencyname}}', emergencyname);
body.replaceText('{{emergencyphone}}', emergencyphone);
body.replaceText('{{emergencyrelation}}', emergencyrelation);
body.replaceText('{{emergencyname2}}', emergencyname2);
body.replaceText('{{emergencyphone2}}', emergencyphone2);
body.replaceText('{{emergencyrelation2}}', emergencyrelation2);
body.replaceText('{{comptravelmag}}', comptravelmag);
body.replaceText('{{secondaryFname}}', secondaryFname);
body.replaceText('{{secondaryLname}}', secondaryLname);
body.replaceText('{{secondarybirthday}}', secondarybirthday);
body.replaceText('{{secondaryprofession}}', secondaryprofession);
body.replaceText('{{secondaryemail}}', secondaryemail);
body.replaceText('{{secondaryphone}}', secondaryphone);
body.replaceText('{{secondarywhatsapp}}', secondarywhatsapp);
body.replaceText('{{secondarypreferredcontact}}', secondarypreferredcontact);
body.replaceText('{{secondaryUScitizen}}', secondaryUScitizen);
body.replaceText('{{secondaryOcitizen}}', secondaryOcitizen);
body.replaceText('{{secondaryTsapre}}', secondaryTsapre);
body.replaceText('{{secondaryPairplane}}', secondaryPairplane);
body.replaceText('{{secondaryPhotelamen}}', secondaryPhotelamen);
body.replaceText('{{secondaryFFlyer}}', secondaryFFlyer);
body.replaceText('{{secondaryhotelloy}}', secondaryhotelloy);
body.replaceText('{{secondaryvistedcountries}}', secondaryvistedcountries);
body.replaceText('{{secondarysmoke}}', secondarysmoke);
body.replaceText('{{secondaryallergies}}', secondaryallergies);
body.replaceText('{{secondaryOthermed}}', secondaryOthermed);
body.replaceText('{{timestamp}}', timestamp);	
        
    //Append tactics
    parseTactics(headers.values[0], tactics.values[i], body);
    
  }

}

function parseTactics(headers, tactics, body){ 
  
  for(var i = 1; i < tactics.length; i++){
    {tactics[i] != '' && 
      body.appendListItem(headers[i] + ' | ' + tactics[i] + ' OTHER').setGlyphType(DocumentApp.GlyphType.BULLET);
    }
    
  }
}

Error: "We're sorry, a server error occurred. Please wait a bit and try again. (line 63, file "Code")"

I expected the script to generate a new google doc from the data sheet as it is being submitted on google form.

Cooper Cooper · Accepted Answer · 2019-07-29T04:57:04

I think your pretty close. Here's an example I did.

First, I created a function to generate some data for myself.

function testData() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clearContents();
  var rg=sh.getRange(1,1,10,10);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      vA[i][j]=Utilities.formatString('row: %s - col: %s',i+1,j+1);
    }
  }
  rg.setValues(vA);
}

The above function creates a sheet that looks like this:

The Template File Looks like this:

And after running this code:

function createDoc(){
  var spreadsheetId='spreadsheet Id';
  var templateId='template Id';
  var data=Sheets.Spreadsheets.Values.get(spreadsheetId,'Sheet177!A1:J2');//range include sheet name
  var docId=DriveApp.getFileById(templateId).makeCopy('Test').getId();
  var body=DocumentApp.openById(docId).getBody();

  for(var i=0;i<data.values.length;i++) {
    for(var j=0;j<data.values[i].length;j++) {
      var s=Utilities.formatString('{{col%s-%s}}',i+1,j+1);
      body.replaceText(s,data.values[i][j]);
    }             
  }
}

There appears in the same folder another file named test that looks like this:

I must say that sure is a simple way to get data.

I'm kind of wondering if perhaps you simply didn't authenticate the program in the script editor.

We're sorry, a server error occurred. Please wait a bit and try again. (line 63, file "Code")

1 Answers