copy range that change constantly into values only in target ranges

16 views
Skip to first unread message

Kenneth Tran

unread,
Nov 24, 2021, 4:26:14 PM11/24/21
to Google Apps Script Community
Hi there,

Need some help.
I want the script to copy Value only from sheet Ranking to sheet ThatDayHI.
The value in the range of Ranking!E36:ZZ36 will be changing constantly, but I want to copy the values to keep track of them (and use them for other calculation). I can write a simple script (below) to copy one time, but how to amend my script so that the copy continues like this, if value in Ranking!B3:ZZ3 different from values in ThatDayHI!E36:ZZ36, then copy them to next line in ThatDayHI!E37:ZZ37, and the copied value in ThatDayHI!E36-ZZ36 WILL stay there and won't be changed or deleted.

function CopyTodayHI1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E36:Zz36').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Ranking'), true);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('ThatDayHI'), true);
  spreadsheet.getRange('Ranking!B3:zz3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

attached is my example file

thanks in advance
kt

Clark Lind

unread,
Nov 26, 2021, 12:23:21 PM11/26/21
to Google Apps Script Community
Hello, The below code assumes 1) you will run this code manually and 2) you are manually filling in the data for columns A-D on the ThatDayHI sheet. 

//Helper functions courtesy of 1LOC (https://1loc.dev/)
const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []); //this function converts a 2-dimensional array to a 1-D array
const isEqual = (a, b) => JSON.stringify(a) === JSON.stringify(b); //this function compares two arrays for equality

function CopyTodayHI1() {
  const ss = SpreadsheetApp.getActive();
  const ranking_Sheet = ss.getSheetByName('Ranking'); //get ranking sheet
  const ThatDayHI_Sheet = ss.getSheetByName('ThatDayHI'); //get ThatDayHi sheet
  const lastDataRow = ThatDayHI_Sheet.getDataRange().getNumRows(); //find the last row of data on ThatDayHi sheet
  const lastHI = flat(ThatDayHI_Sheet.getRange(lastDataRow,5,1,22).getValues()); //get the last row
  const currentHI = flat(ranking_Sheet.getRange('B3:W3').getValues()); //get the current values
  
//compare last data row to current values
    if (isEqual(lastHI, currentHI)) {
      return; //if they are equal return and do nothing
    } else {
      ThatDayHI_Sheet.appendRow([,,,, ...currentHI]) //otherwise, append the current ranking to the ThatDayHi sheet
    }
};


Reply all
Reply to author
Forward
0 new messages