How to set dynamically value from a google spread sheet to another google spread sheet cell

Question

I'm trying to get constract date from handover report google spread sheet,

//here's sample handover report sheet https://docs.google.com/spreadsheets/d/1gVnj2LV60hBXmuiTDa287cNoN1VzroPJEPXl3w-SBF0/edit?usp=sharing

Then, I wanna set the value to cell that match with row including handover report ss id and column including "constract date" text.

//here's sample List sheet https://docs.google.com/spreadsheets/d/1Hu8dTsuH5iS9P0JGBlyN6pOWHo1hhe2t03Wih2BDRGw/edit?usp=sharing

But, nothing happen:( As you see, important to keep row&culumn dynamic for flexibility and expandability.

I sincerely appreciate the help.

function contractDate() {
  
  //handover report ss
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('handover report');
  var val = sh.getRange(2,1).getValue();
  var id = ss.getId();
  
  //list ss
  var lss = SpreadsheetApp.openById('1Hu8dTsuH5iS9P0JGBlyN6pOWHo1hhe2t03Wih2BDRGw');
  var lsh = lss.getSheetByName('before submission');
  var lastCol = lsh.getLastColumn();
  var lastRow = lsh.getLastRow();

  
  //get dynamic column number of contranct date　
  function findCol_contractDate(lsh,lastCol) {
    for (var i=1; i<=lastCol; i++) {
    if(lsh.getRange(1,i).getValue() === 'constract date') {
        return i;
    }
      return 0;
    }
  }
  
  
  // get dynamic column number of handover report
  function findCol_report(lsh,lastCol) {
    for (var i=1; i<=lastCol; i++) {
      if(lsh.getRange(1,i).getValue() === 'ss id of handover report') {
        return i;
      }
      return 0;
    }
  }
  
  //get dynamic row number of ss id that match with the id
  function findRow(lsh,lastRow,findCol_report,id) {
    for(var i=1; i<=lastRow; i++) {
      if(lsh.getRange(i,findCol_report).getValue() === id) {
        return i;
      }
      return 0;
      
    }
  }
  
  //set value
  function setValue(findRow,findCol_contractDate,val) {
   lsh.getRange(findRow,findCol_contractDate).setValue(val);
  }
}

Answer 1

The problem is the way you write your functions

You define all functions inside of contractDate(), but you never call them and never assign them parameters.

Also:

Your return 0; statement should be placed after the for loop - otherwise after the first iteration 0 will be returned if the if condition is not fullfilled. Returning means that the function will halted before the iteration is complete.

Working sample:

//handover report ss
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('handover report');
var val = sh.getRange(2,1).getValue();
var id = ss.getId();

//list ss
var lss = SpreadsheetApp.openById('1Hu8dTsuH5iS9P0JGBlyN6pOWHo1hhe2t03Wih2BDRGw');
var lsh = lss.getSheetByName('before submission');
var lastCol = lsh.getLastColumn();
var lastRow = lsh.getLastRow();

function contractDate() {  
  var dateCol = findCol_contractDate(); 
  var reportCol = findCol_report();
  var row = findRow(reportCol);
  setValue(row,dateCol);
  
}

//get dynamic column number of contranct date　
function findCol_contractDate() {
  for (var i=1; i<=lastCol; i++) {
    if(lsh.getRange(1,i).getValue() === 'contract date') {
      return i;
    }
  }
  return 0;
}


// get dynamic column number of handover report
function findCol_report() {
  for (var i=1; i<=lastCol; i++) {
    if(lsh.getRange(1,i).getValue() === 'ss id of handover report') {
      return i;
    }
  }
  return 0;
}

//get dynamic row number of ss id that match with the id
function findRow(findCol_report) {
  for(var i=1; i<=lastRow; i++) {
    if(lsh.getRange(i,findCol_report).getValue() === id) {
      return i;
    }    
  }
  return 0;
}

//set value
function setValue(findRow,findCol_contractDate) {
  if(findRow > 0 && findCol_contractDate > 0){
    lsh.getRange(findRow,findCol_contractDate).setValue(val);
  }
}