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:

Devices:

Devices After Script:

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.

CodePudding user response：

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;
      }
    }
  }
}

CodePudding user response：

Try this:

Here I used

New ID not found:

Old and New ID found: