I am looking to make a form (which has a Sheets response) where staff will enter a number. I would like to change this number and put something in front of it. Example: they will enter 1234 and on the form it will enter it as AB-CDE-1234. The first part will always be the same so I want to make it easier for them. I am decent in scripting for Sheets but I have never done so with Forms and when submitting to sheets.

Is this even possible? I am confused a little by what I saw in a search, I thought I would save myself time and see if it even if.

CodePudding user response：

I was able to figure this out.

In sheets, you can make any function and attach it to the trigger "On form submit". It will run after a response is submitted, now I just have to work on getting variables and concatenating them.

CodePudding user response：

I used these two functions to insert a similar sort of set of strings into the linked sheets. In my case questions one through six are all short answer question. In the event object x.values[0] is the time stamp and the rest are short answer questions and you can see below how the strings were inserted into the appriate row of the the linked sheet.

Code:

function onMyFormSubmit(x) {
  const ss = SpreadsheetApp.getActive();
  const sh = x.range.getSheet();
  let o = [[1,2,3,4,5,6].map(i => `AB-${randomString(3)}-${x.values[i]}`)];
  Logger.log(JSON.stringify(o));
  sh.getRange(x.range.rowStart,x.range.columnStart   1,o.length,o[0].length).setValues(o);
  
}

function randomString(n) {
  var chars='ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
  var s=chars.charAt(Math.floor(Math.random()*26.0));
  for(var i=0;i<n-1;i  ){
    s =chars.charAt(Math.floor(Math.random()*26.0));
  }
  return s.split('').sort(function(a,b){if(a==b){return 0;}else{return [-1,1][Math.floor(Math.random()*2)];}}).join('');
}

The Ouput: