Google Apps Script – Copy and Paste Cell Contents Once a Week

google docsgoogle-apps-script

As per the title, I want to "move" the contents of a cell one column to the right once a week, specifically Sunday at 11pm

Must I use a script for that?

Best Answer

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

enter image description here

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.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Best Answer

Code

Trigger

Example

Explained

Related Solutions

Google Sheets – Time Triggered Script to Change Cell Contents

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Related Topic