CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Riceman
Riceman

Reputation: 45

How to conditionally change values of a Google Sheet using another Sheet

I am trying to modify an existing Google Sheet using a Form response sheet. Suppose in the sheet I need modified I have column G called "Annotated Asset ID" which contains a list of asset IDs. Now in the form response sheet there are two columns; Old ID (D) and New ID (E). I would like to check Column G of the existing sheet to see if it contains the Old ID and if it does I need to replace it with the New ID.

What is a good way to do this?

Form Response:
Form Response

Existing Sheet:
Existing Sheet

Upvotes: 1

Views: 875

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

You can do this with Apps Script.

Steps:

  • Extract out the old and new IDs from the form response sheet
  • For each of the old IDs, use a TextFinder to search your column for the old ID in the existing sheet:
    • const cell = sheet.getRange("G1:G").createTextFinder("old-id").findNext()
  • Replace the ID if cell isn't null:
    • if (cell) { cell.setValue("new-id") }

Code Example:

function replaceIds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const formResponseSheet = ss.getSheetByName("response-sheet-name")
  const existingSheet = ss.getSheetByName("existing-sheet-name")

  const oldIds = formResponseSheet.getRange("D1:D").getValues().flat()
  const newIds = formResponseSheet.getRange("E1:E").getValues().flat()

  const rangeToCheck = existingSheet.getRange("G1:G")

  oldIds.forEach(function(id, index) {
    let cell = rangeToCheck.createTextFinder(id).findNext()
    
    if (cell) {
      cell.setValue(newIds[index])
    }
  })
}
  • Add this to your project from the Tools > Script editor menu item. Make sure to change the response sheet nad existing sheet names.
  • Save the script and run the function. You will need to authorise it to run on your behalf.

Upvotes: 1

Related Questions