Google Sheets formulas based on two cells dependent of each other - Appscript maybe?

0
votes

I have the following issue:

I have Column A and Column B, the user can input data on either A or B and the sheet should do the calculation regardless of what column the data is entered on:

Example: User can enter 1200 in column A and Column B will display the data /2 (600), but the user can also enter 600 in column B and column A should display the data * 2 (1200), using formulas will not be an option because if there are formulas in both columns and the user enters the information will delete the formula in either or, is there any alternative on Appscript for this?

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Flow:

  • Create a cfg object to do what's to be done on each column edit
  • On a simple edit trigger , check which column and sheet is edited and
  • Do calculations as specified by the cfg object

Sample script:

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} param1
 */
const onEdit = ({ range: rg, value: val }, { columnStart: cs } = rg) => {
  const cfg = {
    1: { offset: 1, calc: num => num / 2 },
    2: { offset: -1, calc: num => num * 2 },
  }[cs];
  /*Only include Sheet1 Column A and B*/
  if (typeof cfg === 'undefined' || rg.getSheet().getName() !== 'Sheet1')
    return;
  rg.offset(0, cfg.offset).setValue(cfg.calc(val));
};

References: