Google Sheets Script – Copy and Paste Data Based on Current Week Date

This piece of script will move the first entry of a row range to the next column:

Code

function myShift() {
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getRange("1:1").getValues();
  var counter=0;
  for(var i=0, iLen=data[0].length; i<iLen; i++) {
    if(data[0][i] == "") {
      counter++;
    } else {
      data[0][counter+1] = data[0][i];
      data[0][counter] = "";
      break;
    }
  }
  sh.getRange("1:1").setValues(data);
}

Trigger

Example

I've created an example file for you: Copy and Paste Cell Contents Once a Week

Explained

Add the script under Script Editor from the Tools menu. Press the "bug" button to debug the code and to authenticate the script. Goto Resources (in the script editor) and set the trigger as shown.

Here is an example of how to do this. When "update" or "edit" is selected from the dropdown boxes, data from the other, designated tab's rows are copied into the current sheet.

Here is the script:

function onEdit(e) {

////UPDATE ROWS WITH DATA FROM REMOTE SHEET VIA SCRIPT
///the script below is adapted from https://mashe.hawksey.info/2015/03/google-sheets-move-a-row-of-data-to-another-sheet-based-on-cell-value/
  // see Sheet event objects docs
  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getSheetByName("Animals List");
var r = e.range;
var remoteSheet = ss.getSheetByName("List B");

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;

var curNumberCell = s.getRange(rowIndex,2, 1, 1);
var curIDNumber =  curNumberCell.getValue();
var curDCell = s.getRange(rowIndex,4, 1, 1);
var curECell = s.getRange(rowIndex,5, 1, 1);

 // if our action/status col is changed to update or edit, do stuff
if (e.value == "update" || "edit" && colIndex == actionCol) {

 //THE FOLLOWING REPLACES THE FORMULAS OF THE ROW WITH THEIR GENERATED VALUES - UNCOMMENT TO USE:
 //var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
 //sourceRange.setValues(sourceRange.getValues());


///Retrieve data from remote tab/sheet that has matching cell value
var values = remoteSheet.getDataRange().getValues(); //IN REMOTE SHEET WE NEED TO SEARCH ENTIRE SHEET

for (var i = 0; i < values.length; i++) {
   if (values[i][0] == curIDNumber )
   {
   curDCell.setValue(values[i][1] );
   curECell.setValue(values[i][2] );
   }
   }    
} //end retrieve remote data
}