Google sheets How to create a custom function

user_10079419 0
Viewed 138

I have two sheets

Sheet1 enter image description here

and sheet2 enter image description here

What I want to do is one by one copy the values of each row from sheet 1 to sheet 2. whenever one row is copied from sheet1 to sheet2 (i.e values from cell A to F). the cell G2 and H2 (in sheet2) get values ( based on multiple different formulas filter function, pivit tables etc from other sheets ) When I get the updated values in sheet 2 I want to copy the values form G2 and H2 to the respective row back in sheet 1

basically I want to use the sheet2 as a custom function where cell A2, B2, C2, D2, H2, F2 are inputs and cell G2 and H2 are outputs.

I tried using macros but what is required is a combination of both Absolute and Relative referencing and that doesn't seem to be possible

gif of the process I am doing manually enter image description here

google-sheets
1 Answers

Did it with appscript and menue items

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('AERON')
      .addItem('Calculate Single Cabnets', 'menuItem1')
      .addSeparator()
      .addItem('Calculate Multiple Cabnets', 'menuItem2')
      .addToUi();
}
 
function menuItem2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
 
  if(selection.getActiveRange().getA1Notation()==null)
  ui.alert('No range selected');
 
  else{
    //get the inputs 
    var sheetname = SpreadsheetApp.getActiveSheet().getName();
    // SpreadsheetApp.getUi().alert(sheetname);
 
    var range = SpreadsheetApp.getActiveSpreadsheet().getRange(selection.getActiveRange().getA1Notation());
    var input = range.getValues();
 
    var ac = SpreadsheetApp.getActiveSpreadsheet().getActiveRange()
    SpreadsheetApp.getUi().alert(ac);
 
    // get sheet name 
    sheetname = SpreadsheetApp.getActiveSheet().getName()
 
    const numRows = ac.getNumRows();
    for (let i=1; i<= numRows;i++ ){
      var cell = ac.getCell(i, 1).offset(0, 0,1,6).getA1Notation();
      var cell1 = ac.getCell(i, 1).offset(0, 7,1,2).getA1Notation();
      // SpreadsheetApp.getUi().alert(cell + " " +sheetname + " " + cell1);
      single_row(sheetname, cell, cell1 );
 
    }
 
    SpreadsheetApp.getUi().alert("All values updated")
 
  }
}
 
function single_row(sheetName, inputRange, outputRange ) {
  var fnSheetName = "Main";
  var fnInputRange = 'A2:F2';
  var fnOutputRange = 'G2:H2';
 
  var input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(inputRange).getValues();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(fnSheetName).getRange(fnInputRange).setValues(input);
  var output = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(fnSheetName).getRange(fnOutputRange).getValues();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(outputRange).setValues(output);
}

This creates a menue item on the top which one by one copies the values to the other sheet then copies the result back

user_100794190
Related