I would like to make this script work horizontally.

This code is from another answer here, but I have not been able to make it work horizontally. I need to use it across columns in one row, calculating gaps in datasets.

I have no idea what im doing and make a mess every time I try to edit it.

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Interpolate",  functionName: "interpolate"}]);

function interpolate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var cell = sheet.getActiveCell();
  if (cell.getValue() !== '') {
  var row = cell.getRow();
  var column = cell.getColumn()
  var values = sheet.getRange(1, column, height, 1).getValues();
  for (var i=row-1; i>1; i--) {
    if (values[i-1][0] !== '') {
  for (var j=row+1; j<height; j++) {
    if (values[j-1][0] !== '') {
  var a = values[i-1][0];
  var b = values[j-1][0];
  if (typeof a == 'number' && typeof b == 'number') {
    var insert = [];
    for (var k=1; k<j-i; k++) {
    sheet.getRange(i+1, column, j-i-1, 1).setValues(insert);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.