How do write script for below

google form response as below

time stamp Document No. Recipient Name Document transmittal ref no
7/9/2021 21:36:11 170709, 170738, 171769, 172003, 172025 Susan 20210001
7/18/2021 18:19:59 131034, 131192, 132438, 132465, 133016 Jhon 20210002

i want processed data look like below

Time stamp Document no. Recipient Name Document transmittal Ref no.
7/9/2021 21:36:11 170709 Susan 20210001
7/9/2021 21:36:11 170738 Susan 20210001
7/9/2021 21:36:11 171769 Susan 20210001
7/9/2021 21:36:11 172003 Susan 20210001
7/9/2021 21:36:11 172025 Susan 20210001
7/18/2021 18:19:59 131034 Jhon 20210002
7/18/2021 18:19:59 131192 Jhon 20210002
7/18/2021 18:19:59 132438 Jhon 20210002
7/18/2021 18:19:59 132465 Jhon 20210002
7/18/2021 18:19:59 133016 Jhon 20210002

the current script was done below

function formatData() {
 // File
 var ss = SpreadsheetApp.getActiveSpreadsheet();

 // Get responseData sheet
 var rawData = ss.getSheetByName('documentTransmittal');

 // Input data
 var data = rawData.getRange("A2:D").getValues(); // Gets titles and options in a single call to the sheet

 // Initialise an array that will hold the output
 var outputArray = [];

 // Name a variable to hold the data from each set of options
 var options;

 // Start looping through the data
 for (var row = 0; row < data.length; row++) {

 // Split the document with individual no.
 options = data[row][1].split(", ");

 // Loop through the array of split options and place each of them in a new row
 for (var element = 0; element < options.length; element++) {

 outputArray.push([data[row][0], // Place the title in a new row
                  options[element]]); // Place one option in the 2nd column of the row

  } // Options loop ends here

 } // Data loop ends here

 // Get processedData sheet
 var processedData = ss.getSheetByName('responseData');

 // Get last row in processedData sheet
 var lastRow = processedData.getLastRow();

 // Post the outputArray to the sheet in a single call
 processedData.getRange(lastRow + 1, 1, outputArray.length,


above only can help get 2 row data only


Your code is OK but there are a few areas where it could be improved.

Last Row

As it is written, the code converts the entire "documentTransmittal" sheet. In this case, the last row will always be zero and there is nothing be gained by using var lastRow = processedData.getLastRow();

Data Range

Specifying an entire column ("D") means that the range could be 1000 rows (or more) deep even though the actual data might be a couple of rows. This is not a particular problem, but the getDataRange method is an alternative to select only the range containing data.

  • Delete: var data = rawData.getRange("A2:D").getValues();
  • Substiute: var data = rawData.getDataRange().getValues();

Creating each row

Your code only inserts the Transmission time and the elements of the Document Number. The needs to be expanded to include the "Recipient Name" and the "Document transmittal ref".

  • Delete: outputArray.push([data[row][0], // Place the title in a new row options[element]]); // Place one option in the 2nd column of the row
  • Substitute: outputArray.push([data[row][0], // Place the title in a new row options[element],// Place one option in the 2nd column of the row data[row][2], // Place the Recipient name data[row][3]]); // Place Ref No.

Sample data


Script Output


  • Thanks for your advise, appreciated – hanz cenz Jul 21 at 9:34
  • i found a new issue here, the old data will be repeated (copy and paste) when i run the script the doc transmittal is a response data that collected from google form the data will always updated when received the respond from responder how do i solve it with script ? – hanz cenz Jul 21 at 9:37
  • Yes, I wondered about that but you didn't mention it it in your question. You need to modify your script to reflect a couple of things. First, trigger the script with an installable [onFormSubmit](https://developers.google.com/apps-script/guides/triggers/installable) trigger. Enable event objects to get the new response. Last, append only the new response. – Tedinoz Jul 23 at 0:26
  • I suggest that you do some research on onFormSubmit triggers - there are lot of precedents on Webapps and StackOverflow. If you get stuck, raise it as a new question. – Tedinoz Jul 23 at 0:30
  • Just still stuck at there, I tried to set the triggers to run the script, but the no one is show the result as wanted – hanz cenz Jul 23 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.