CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Riceman
Riceman

Reputation: 45

How to Conditionally Change Data in Google Apps Script for Sheet

The problem I am trying to solve involves a google sheet with two separate sub-sheets called "Devices" and "Form Responses." I am trying to automate a Google Form response to update a sheet. The Form Response sheet gives three relevant columns: user_name, an old asset ID and a new asset ID. In the Devices sheet there are two relevant columns: user_name and asset ID. I want the script to parse through the asset ID column in the Devices sheet looking for matches from the old asset ID list. If there is a match it should replace that field with "" and then find the new asset ID in the Devices list and assign the user_name provided in Form Responses to that row.

Form Response:

user_name old asset ID new asset ID
joe 1234 9876

Devices:

user_name asset ID
joe 1234
null 9876

Devices After Script:

user_name asset ID
null 1234
joe 9876

The way I am currently trying to do it produces errors if a field is null so I am really looking for a nudge in the right direction. I have programming experience, but apps scripts is not like other languages I have used and I am having a hard time finding documentation on it. I am having trouble implementing the logic I would normally use.

Upvotes: 1

Views: 310

Answers (2)

Nikko J.
Nikko J.

Reputation: 5543

Try this:

Here I used Range.createTextFinder(string) to search for the old asset ID and new asset ID in the Asset ID column of Devices. It will return a TextFinder Class which has findNext() method that will return the range of the first matching cell and used that range to determine which cell to edit.

function onFormSubmit(e) {
  var responses = e.namedValues;
  var username = responses['user_name'][0];
  var oldId = responses['old asset ID'][0];
  var newId = responses['new asset ID'][0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var deviceSheet = ss.getSheetByName("Devices");

  var oldIDFinder = deviceSheet.getRange(2, 2, deviceSheet.getLastRow()-1, 1).createTextFinder(oldId).findNext();
  if(oldIDFinder){
    var newIDFinder = deviceSheet.getRange(2, 2, deviceSheet.getLastRow()-1, 1).createTextFinder(newId).findNext();
    if(newIDFinder){
      deviceSheet.getRange("A"+oldIDFinder.getRow()).clear();
      deviceSheet.getRange("A"+newIDFinder.getRow()).setValue(username);
    }  
  }
}

Note: This will not work if either old asset id or new asset id is not found in the range.

Examples:

Old ID not found:

enter image description here

enter image description here

New ID not found:

enter image description here

enter image description here

Old and New ID found:

enter image description here

enter image description here

Upvotes: 0

Cooper
Cooper

Reputation: 64140

You can probably start with something like this:

function onFormSubmit(e) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Device');
  const sr = 2;//data start row
  const hr = 1;//header row
  const hA = sh.getRange(hr, 1, 1, sh.getLastColumn()).getValues()[0];
  let idx = {};
  hA.forEach((h, i) => { idx[h] = i; });
  let found = false;
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
  for (let i = 0; i < vs.length; i++) {
    let r = vs[i];
    if (r[idx['asset ID']] == e.namedValues['old asset ID'][0]) {
      sh.getRange(i + sr, idx['user_name'] + 1).setValue('');
      found == true;
      break;
    }
  }
  if (found) {
    for (let i = 0; i < vs.length; i++) {
      let r = vs[i];
      if (r[idx['asset ID']] == e.namedValues['new asset ID']) {
        sh.getRange(i + sr, idx['user_name'] + 1).setValue(e.namedValues['user_name'][0]);
        break;
      }
    }
  }
}

Upvotes: 0

Related Questions