CODEWITHSUNDEEP
google-apps-script
Solenoid
Solenoid

Reputation: 2381

Dynamically update cells after form submission

I'm trying to make a user list based on a code, simply put we have a system where every member of our school has an assigned code, with this code I can get their name and e-mail by making some regexes on our website (basically data mining).

The idea is to enter only this user code (let's say 0123456) and a script will get the data from the website and fill adjacent column values. The script must run on the on form subit trigger, so it's updated only when needed.

I've successfully implemented the data gathering system, but I'm struggling in the filling of the adjacent cells.

// this function is called when form is submitted
function onNewEntry(e)
{
  // get the code (cell 0 is the timestamp)
  userCode = e.values[1];

  // add values and return
  return e.values.concat(["name", "e-mail"]);
}

I expected the script to change the cells in columns C and D with "name" and "e-mail", but nothing happens. Can anybody advise?

Upvotes: 0

Views: 3454

Answers (2)

Serge insas
Serge insas

Reputation: 46792

This function when triggered by on Form submit will write data in column C and D

(I used xx and yy just to illustrate)

function myFunction(e) {

var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

var last = sh.getLastRow()

sh.getRange(last,3,1,2).setValues([['xx','yy']])
}

Upvotes: 3

HardScale
HardScale

Reputation: 1021

Range.setValues([Array]) is your solution. Note the 's' in setValues(...).

https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)

Also, I haven't tested this, but I don't think the object (e) refers to the actual cells, but a copy of the submitted data. I think you'll need to open the sheet and get the range if you want to modify or append to the result.

Upvotes: 0

Related Questions