How to Find & Replace Value in Google Apps Script?

Question

I'm building a simple data entry form in Google Sheets. The user completes specific fields (in Sheet A) and then clicks the Submit button. The data is then entered into another sheet (Sheet B).

I'm looking for a scripting solution that will take the value from the data entry form (Sheet A), do a find and replace (In the script), then place the replaced value in Sheet B. Once the data is entered into Sheet B, the values in Sheet A will be nulled automatically.

Examples:

User entered value is PASS. Value entered on data form is 1.
User entered value is FAIL. Value entered on data form is 0.

Link to Google Sheet: https://docs.google.com/spreadsheets/d/10c1EX27WbINQOjN0UyE778gl8LguKp7MRKwQdaw0mu4/edit?usp=sharing

Script:

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Form Data"); //Data Sheet
  
  //Input Values
  var values = [[formSS.getRange("L5").getValue(),
                 formSS.getRange("D18").getValue(),
                 formSS.getRange("D21").getValue(),
                 formSS.getRange("R18").getValue(),
                 formSS.getRange("R21").getValue(),
                 formSS.getRange("AF18").getValue(),
                 formSS.getRange("AF21").getValue(),
                 formSS.getRange("D33").getValue(),
                 formSS.getRange("D36").getValue(),
                 formSS.getRange("R33").getValue(),
                 formSS.getRange("R36").getValue(),
                 formSS.getRange("AF33").getValue(),
                 formSS.getRange("AF36").getValue(),
                 formSS.getRange("D48").getValue(),
                 formSS.getRange("D51").getValue(),
                 formSS.getRange("R48").getValue(),
                 formSS.getRange("R51").getValue(),
                 formSS.getRange("AF48").getValue(),
                 formSS.getRange("AF51").getValue(),
                 formSS.getRange("D63").getValue(),
                 formSS.getRange("D66").getValue(),
                 formSS.getRange("R63").getValue(),
                 formSS.getRange("R66").getValue(),
                 formSS.getRange("AF63").getValue(),
                 formSS.getRange("AF66").getValue()]];
  
  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 25).setValues(values);
}

Can anyone help?

can you share your script so far? Cant see it from the shared doc — James Rushford
@TravisChristianson For documentation purposes, accept the answer if it solved your question. — soMario

soMario soMario · Accepted Answer · 2020-09-16T15:56:57

Explanation:

User entered value is PASS. Value entered on data form is 1.

User entered value is FAIL. Value entered on data form is 0.

Since you already have the values, you can achieve the above transformation using the following ternary operator:

var f_values = values.map(val=>val=='Pass'?1:val=='Fail'?0:val);

The latter will iterate over the values array and it will transform every value to: 1 if val=='Pass', 0 if val=='Fail' or keep as is if the other two cases evaluate to false.

Solution:

This is what you are looking for:

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Form Data"); //Data Sheet
  
  var values = [formSS.getRange("L5").getValue(),
                 formSS.getRange("D18").getValue(),
                 formSS.getRange("D21").getValue(),
                 formSS.getRange("R18").getValue(),
                 formSS.getRange("R21").getValue(),
                 formSS.getRange("AF18").getValue(),
                 formSS.getRange("AF21").getValue(),
                 formSS.getRange("D33").getValue(),
                 formSS.getRange("D36").getValue(),
                 formSS.getRange("R33").getValue(),
                 formSS.getRange("R36").getValue(),
                 formSS.getRange("AF33").getValue(),
                 formSS.getRange("AF36").getValue(),
                 formSS.getRange("D48").getValue(),
                 formSS.getRange("D51").getValue(),
                 formSS.getRange("R48").getValue(),
                 formSS.getRange("R51").getValue(),
                 formSS.getRange("AF48").getValue(),
                 formSS.getRange("AF51").getValue(),
                 formSS.getRange("D63").getValue(),
                 formSS.getRange("D66").getValue(),
                 formSS.getRange("R63").getValue(),
                 formSS.getRange("R66").getValue(),
                 formSS.getRange("AF63").getValue(),
                 formSS.getRange("AF66").getValue()];
  
  var f_values = values.map(val=>val=='Pass'?1:val=='Fail'?0:val);
  
  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 25).setValues([f_values]);
}

References:

Array.prototype.map()

Conditional (ternary) operator

How to Find & Replace Value in Google Apps Script?

1 Answers

Explanation:

Solution:

References: