Passing a column of string from one Google Sheet to another and readback corresponding outputs calcu-CodePudding

I have 2 Sheets in the same Google Workbook file:

Sheet #1 : Contains some string values in cells A1:A100
Sheet #2: Has already been designed to calculate a numeric output in cell D1 based on the string inputted in cell C1

I want to run a loop which allows passing each entity of Sheet1 A1:A100 one-by-one to Sheet2 C1. Next I want to readback the corresponding Sheet2 output from cell D1 and populate it in Sheet1 cells B1:B100.

Question: Is there some way to achieve this by coding in Google Sheets itself? If not, is there some other way such as using Python?

CodePudding user response：

Description

Here is a Google App Script that will loop through the symbols and return the calculated value from Sheet2!D1.

It is executed from a new menu item Test.

Code.gs

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  let menu = ui.createMenu("Test");
  menu.addItem("Run Test","runTest");
  menu.addToUi();
}

function runTest() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    let symbols = sheet.getDataRange().getValues();
    symbols.forEach( symbol => getResults(symbol) );
    symbols = symbols.filter( symbol => symbol[0] !== "" );
    sheet.getRange(1,1,symbols.length,symbols[0].length).setValues(symbols);
  }
  catch(err) {
    SpreadsheetApp.getUi().alert("Error in runTest: " err);
  }
}

function getResults(symbol) {
  try {
    if( symbol[0] === "" ) return;
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    sheet.getRange(1,3).setValue(symbol[0]);
    SpreadsheetApp.flush();
    symbol[1] = sheet.getRange(1,4).getValue();
  }
  catch(err) {
    SpreadsheetApp.getUi().alert("Error in getResults: " err);
  }
}

Reference