possible options for Cell Mapping in Google Sheet-CodePudding

I have a Google sheet, has two worksheets with cell "A1" as drop down:

Completed
Not Completed
Show All

I want to link both cells so that change in value of one cell will depict in other vise versa. I have been looking for the solution and found out that on selection event the app script can be triggered where I can manipulate values. however I could not figure it out the right solution.

In excel/word we can use XML to map values/controls. what is the alternative for this in Google sheets? there must be a way besides app scripts.

CodePudding user response：

Syncing two cells in two spreadsheets:

I used the same sheet name and you have to put the other ssid in the ss spreadsheet and both have to be installable triggers

function onMyEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if(sh.getName() == 'Sheet0' && e.range.columnStart == 1 && e.range.rowStart == 1) {
    //e.source.toast("Gate1");
    let ss = SpreadsheetApp.openById('other ssid');
    let sh = ss.getSheetByName('Sheet0').getRange("A1").setValue(e.value);
  }
}

I created the validations manually

CodePudding user response：

From I have a Google sheet, has two worksheets with cell "A1" as drop down:, I understood your question is as follows.

You have a Google Spreadsheet including 2 sheets like "Sheet1" and "Sheet2".
The cell "A1" of each sheet has a dropdown list including 3 values like Completed, Not Completed, Show All.
For example, when the dropdown list of "Sheet1" is changed, you want to change the dropdown list of "Sheet2" to the same value with "Sheet1", and vice versa.
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and please set your 2 sheet names to sheetNames and save the script.

When you want to test this script, please change the dropdown list of the cell "A1" of "Sheet" or "Sheet2". By this, the value of your selected dropdown list is copied to the cell "A1" of another sheet, and vice versa.

function onEdit(e) {
  const sheetNames = ["Sheet1", "Sheet2"]; // Please set your sheet names.

  const { range, source, value } = e;
  const sheet = range.getSheet();
  const sheetName = sheet.getSheetName();
  const idx = sheetNames.indexOf(sheetName);
  if (idx == -1 || range.getA1Notation() != "A1") return;
  source.getSheetByName(sheetNames[idx ^ 1]).getRange("A1").setValue(value);
}

Note:

In this sample script, the simple trigger is used. So, when you directly run the script with the script editor, an error like Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.

Sample script:

Note:

References: