CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
james_dean
james_dean

Reputation: 1517

Querying a Column in a Google Script

I've setup a script to copy and process a spreadsheet that is generated from a form.

I gather the responses like this:

  var responses = formResponses.getDataRange().getValues();

Then accessing the individual columns through the array index, for example:

  var timestamp = row[0];
  var agentName = row[1];

Now, while this works. Any changes to the form could end up screwing the whole thing up and the array indices will change.

Can I instead query by column name?

Upvotes: 0

Views: 113

Answers (2)

Daniel Wren
Daniel Wren

Reputation: 290

If accessing it via the onFormSubmit event is not viable for you because you need to access the data post-submission, I have created code that allows you to access via column name.

function getDataFields(workingRow){
    //header is row 1 -- not row 0
    var headerRow = 1;
    var headerRowValues = getRowRange(headerRow).getValues();
    var workingRowValues = getRowRange(workingRow).getValues();
    var dataFields = [];

    for (var colNum in headerRowValues[0]){
        dataFields[headerRowValues[0][colNum]] = workingRowValues[0][colNum];   
    }
    dataFields['Working Row'] = workingRow;

  return dataFields;  
}

function getRowRange(workingRow){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = activeSheet.getLastColumn();
  return activeSheet.getRange(workingRow, 1, 1, lastColumn);
}

You can then use dataFields['Column Name'] to access the data of that particular row.

Upvotes: 1

Serge insas
Serge insas

Reputation: 46822

if you use the onFormSubmit event you can access all form values by their named values, this is explained in the documentation on Spreadsheet Form Submit Events so you won't have this problem anymore...

Upvotes: 0

Related Questions