Find and replace the text in a cell with Google Script and Google Sheets

Question

I have a Google Sheet where I collect responses and another tab where I see a report for each record.

I want to add manually a number in the cell Writing Points (as in the image below) and click Update to update the cell of that specific record in the responses tab.

I managed to get the row number depending on the student the formula is:

MATCH($D$3,Responses!D:D, 0)

And the column is always BG of the repsonses tab.

How can I achieve this through Google script? I have an idea but I don't know how to do it.

My try:

row = MATCH($D$3,Responses!D:D, 0)
SpreadsheetApp.getActiveSheet().getRange('BG'+row).setValue(newwritingpoints);

I don't know how to conver the match formula into Google script syntax and how to copy and paste the value from that cell to the responses tab.

Google Sheet link:

https://docs.google.com/spreadsheets/d/1dE6UVABhVp7WqEFdC0ptBK2ne6wEA6hCf0Wi6PxcMWE/edit#gid=2032966397

Tanaike Tanaike · Accepted Answer · 2020-06-02T00:39:58

I believe your goal as follows.

When Update() of Google Apps Script is run by the button, you want to retrieve the cells "D3" and "H24" in the sheet Report.
You want to search the value of "D3" from the column "D" in the sheet Responses.
When the value of "D3" and the values of column "D" in the sheet Responses are the same, you want to put the value of "H24" to the same row of the column "BG" in the sheet Responses.

For this, how about this answer? The flow of this sample script is as follows.

Retrieve the values from the cells "D3" and "H24" in the sheet Report.
Retrieve the values from the cells "D2:D" in the sheet Responses.
Create the range list from the retrieved values.
Put the value using the range list.

Sample script:

function Update() {
  var spreadsheet = SpreadsheetApp.getActive();

  // 1. Retrieve the values from the cells "D3" and "H24" in the sheet `Report`.
  var reportSheet = spreadsheet.getSheetByName("Report");
  var searchText = reportSheet.getRange("D3").getValue();
  var writingPoints = reportSheet.getRange("H24").getValue();

  // 2. Retrieve the values from the cells "D2:D" in the sheet `Responses`.
  var responsesSheet = spreadsheet.getSheetByName("Responses");
  var values = responsesSheet.getRange("D2:D" + responsesSheet.getLastRow()).getValues();

  // 3. Create the range list from the retrieved values.
  var ranges = values.reduce((ar, [d], i) => {
    if (d == searchText) ar.push("BG" + (i + 2));
    return ar;
  }, []);

  // 4. Put the value using the range list.
  responsesSheet.getRangeList(ranges).setValue(writingPoints);
}

In this script, it also supposes the case that the value of "D3" might find at several rows in the sheet Responses.

Find and replace the text in a cell with Google Script and Google Sheets

1 Answers

Sample script:

References: