Google-apps-script – How to change position of column form responding sheet after add form item

google-apps-scriptgoogle-forms

I can add an item to a Google form and to change its position in the form by Google apps script. The corresponding column in the responding sheet is always the last column. I can manually move that column to the position I want (same position as the item in the form), but how do I move it using Google apps script?

Best Answer

It is not possible for a script to rearrange columns linked to a form. The sheets and columns linked to a form are special, and this special nature is not something Apps Script can see. (Explanation below).

Alternative approach

Create another sheet, Real Form Data, in which the first row is filled with formulas such as

={'Form Responses 1'!A:A}, ={'Form Responses 1'!C:C}, ={'Form Responses 1'!B:B}

in whatever order you wish. These formulas can be manipulated by a script in any way you want, with setFormula(s) methods. You can hide the original sheet with form data, and work only with this new one, which has the desired order of columns.

Explanation

There is no script method for "moving" the way we move rows and columns in the interface. Their surrogates, described in Moving a column in Google Spreadsheet, are

Insert a column, copy values there, delete the original.
Take values of the entire sheet, rearrange them script-side, then write back.

Method 1 doesn't work for form data because the new column wouldn't be linked to the form (and the original column can't be deleted).

Method 2 doesn't work because changing the values in columns doesn't affect their invisible link to the form: it will still send data in the same way as it did.

Related Solutions

Google Sheets to Form – Insert Blank Fields with Answers

Here is a solution that works:

function ApendResponses() {
  var form = FormApp.openById("1M-mTBlZPRj44jXs_YHj-cNa9yHjU25ItEWBuKtUvKZo");
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Jc9NVlN-Wcmd87E-FnyocHgl5Vx2WXqOGPTkhvXjCFU/");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var x = 0; x < values.length; x++) {

    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = values[x];

    var formItem = items[0.0].asTextItem();
    if (!isempty(row[1])){
    var response = formItem.createResponse(row[1]);
    formResponse.withItemResponse(response);}

    var formItem = items[1.0].asTextItem();
    if (!isempty(row[2])){
    var response = formItem.createResponse(row[2]);
    formResponse.withItemResponse(response);}

    var formItem = items[2.0].asListItem();
    if (!isempty(row[3])){
    var response = formItem.createResponse(row[3]);
    formResponse.withItemResponse(response);}

    var formItem = items[3.0].asPageBreakItem();                       
    formResponse.withItemResponse(response);

    var formItem = items[4.0].asListItem();
    if (!isempty(row[4])){
    var response = formItem.createResponse(row[4]);
    formResponse.withItemResponse(response);}

    var formItem = items[5.0].asPageBreakItem();
    formResponse.withItemResponse(response);

    var formItem = items[6.0].asListItem();
    if (!isempty(row[5])){
    var response = formItem.createResponse(row[5]);
    formResponse.withItemResponse(response);}

    var formItem = items[7.0].asPageBreakItem();
    formResponse.withItemResponse(response);

    var formItem = items[8.0].asTextItem();
    if (!isempty(row[6])){
    var response = formItem.createResponse(row[6]);
    formResponse.withItemResponse(response);}

    formResponse.submit();
    Utilities.sleep(0.01);

  }

}

function isempty(entry) {
  if (entry == undefined)
  {
    return true;
  }

  if (entry == null){
   return true; 
  }
   var tempstr = entry.toString();

   tempstr = tempstr.replace(/[\r\n\t\s]+$/,"");
   tempstr = tempstr.replace(/^[\r\n\t\s]+/,"");
  if (tempstr.length == 0){

    return true;
  } else {

    return false;
  }

}

This solution has two issues:

It doesn't stop, i.e. after the script processes the last row, it keeps on adding blank rows. This is easily dealt with: the script needs to be stopped after the correct amount of answers got injected, and any surplus answers can be individually deleted from the Forms' "Responses" tab.
There is a 6min time limit on running scripts. Thus this script cannot handle plenty of answers (definitely not more than 500). Workarounds can be made by better scripting (stop, record answers processed, then run again from there until the job's done) or by splitting answers into several sheets and running them one after the other.

Source.

Google-sheets – How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet

Google Apps Script hasn't any trigger that is triggered by formula results changes, so you have to rethink how to get the result what you are looking for from a broader perspective. Maybe instead of looking for a formula change you should think about what makes the formula to be recalculated and which of those recalculations are relevant.

By the way, formulas are recalculated every time that a spreadsheet is opened. Built-in functions are recalculted when a change is made to the spreadsheet, some volatile functions like NOW could be recalculated on change and every certain time. Import functions like IMPORTRANGE officially are recalculated every certain number of minutes (30 minutes for IMPORTRANGE) but the practice tell us that under certain conditions they are recalculated as soon as the source range change. Custom functions are recalculated on spreadsheet opening and are recalculated when one of their arguments changes but they can't use some volatile built-in functions, like NOW, as arguments.

A some sort of "general solution" could be to use a time-driven trigger to poll the spreadsheet or a specific cell for changes and if there is any then add the timestamp but this should be done (very?) carefully to avoid to exceed the Google Apps Script quotas.

References

Why don't work function onEdit(e) when using Zapier