How do I automatically match text to numbers and sum those numbers in Google Sheets/ Forms?

I am using a google form to collect responses from students. For each student I would like to match responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for each student. So if a student answered five questions with “Almost Never” the end result would be 5. Responses are fixed allowing for a vlookup or match function.

Since it’s a google form, I’ve been using an ArrayFormula to sum each student’s responses. Something like this…

=ArrayFormula(G2:G+H2:H+I2:I+J2:J)

However this requires me to have a separate vlookup for each column to match the text to number. If I manually enter vlookup for each row, I can neatly combine vlookup and sum in one formula such as this…

=ArrayFormula(sum(vlookup(G2:S2,$A$10:$B$14,2,False)))

However this does not work for any future students. Is there a way to nest these formulas? Or another option? Ideally I would like to have one formula that for each row automatically matches fixed text responses to numbers, sums those numbers and can accommodate future rows/students responding.

Thanks for any help!

Go to Source
Author: jhaeff

Google Apps Script: Check, Copy, Email and Delete Duplicate row from Google Form Submission

Need your help on this, i am totally new to the Google Apps Script and struggling with the small function of following:-

  1. Check for the duplicate on specific field (only one field) value submitted from Google Form
  2. If no duplicate found, do nothing.
  3. If duplicate found, script will copy the entire duplicate row found to the another sheet in the same spreadsheet.
  4. Script will then send an email to the responder automatically.
  5. Script will then delete the duplicate row and wait for the next submission.

I’ve a snippet of the modified script getting from the online resources, and play around it but unfortunately looks like it went so much wrong and not works as per expected.

What i have:-

  1. In my Google Form, I’ve only capture one field value as Short Answer text field named Serial
  2. In my Form Responses sheet only contains three column (timestamp, Serial & Email address collected from those responded into the form)
  3. Duplicate Response sheet, contains the same header as in Form Responses (will use to copy the entire duplicate records row here before actually delete it from the Form Responses Sheet).
  4. My code is as below:-
function removeDuplicates()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var targetsheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Duplicate Responses")
  var targetNewRow = targetsheet.getLastRow() + 1;
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var indexOfStatusColumn = 1;
  var newData = [];
  data.forEach(function (row, rowI){
    var isDuplicate = false
    if (newData.length)
    newData.forEach(function (dup, dupI)
    {
      if (dup[3] == row[3])
      {
        if (row[indexOfStatusColumn] == dup[indexOfStatusColumn])
        {
          targetsheet.appendRow(row);
          sendEmail(row[2],row[1]);
          isDuplicate = true;
 
        }
        return (false);
      }
    })
    if (!isDuplicate)
      newData.push(row);
  });
  dataRange = sheet.getRange(2, 1, dataRange.getLastRow() - 1, dataRange.getLastColumn());
  sheet.deleteRow(dataRange.getLastRow() - 1);
  //dataRange.clearContent();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

function sendEmail(emailaddr, sn) {}

Issue:-

  1. The initial code was written originally with clearContent() function, but apparently i found once the contents is cleared and new submissions is made it will actually appended only next after the empty row which caused it left the duplicate row empty and not reusable.

  2. The duplicate row copied to the another sheets “Duplicate responses” is not as expected, it will also copied non-duplicate records intermittently when mass submission is made.

Appreciate your help on this please!

Go to Source
Author: Fariz Azmi

Sheets – Aggregate checklist responses from Google forms with “Other” as an option

(Note the “Other” choice, so my question is not a duplicate unless I overlooked the question where it was asked when searching).

On a Google form, I made a checkbox question in the following format:

What flavors do you like?

[] Chocolate

[] Vanilla

[] Strawberry

[] Other: ________

When passing responses to Google Sheets, I get something like:

Chocolate, Vanilla, Apple

Vanilla, Strawberry, Coconut

Chocolate

Chocolate, Strawberry

I want to aggregate my responses in a Google sheet in the following way (in any order):

Chocolate 3

Vanilla 2

Strawberry 2

Apple 1

Coconut 1

How can I achieve this?

Go to Source
Author: simplegamer

Form to Collecting Students’ Cell Numbers

I am a college instructor and I will be teaching Summer classes next week. My school allows instructors to broadcast time-sensitive urgent message to students’ cellphones. Therefore I would like to collect my students’ numbers.

I am wondering if there is any free online-form out there (Google spreadsheet, form perhaps) that I can email to students, asking them to enter their cell numbers but they won’t see others’ numbers, thus satisfying my school’s privacy policy.

I am a computer-savvy person but definitely not a heavy code-writer. Thank you for your time and helps.

Go to Source
Author: A.Magnus