Google-sheets – User defined Series in Google Sheets

google sheets

I have a series which is like 1,1,1,1,1,1,1,2,2,2,2,2,2,2…

I have filled 1(*8) times and 2(*8) times, how do I continue this series by drag-drop option in Google Sheets?

Best Answer

We need to develop a custom function for doing the required tasks.

/* _val is the number of series you want
*  you want 166 series of the number which is like 166*8
*/ the function will fill 1328 rows

function myFunction(_val) {

  var data = [];

  for(var x =1 ; x < _val ; x++){
    // here 8 is the number of times you want the number to repeat
    for(var l = 0 ; l < 8 ; l++){
        data.push(x);
    }

  }
  return data;
}

Points to be noted:

You can return a multidimensional array like [[1,2] [4,5]] to fill up multiple columns at a time // here the result will be Cell A will have 1,4 and B will have 2,5
You can't use setValue() in a custom function

Related Solutions

Google Sheets – How to Plot a Time Series Graph

Google should now respect X-axis date values for Line, Area, Scatter, and Time Line charts.

For example, if you have the following data*:

|    Date    | Value |
|------------|-------|
| 01/01/2016 |   3   |
| 02/01/2016 |   4   |
| 03/01/2016 |   5   |
| 03/15/2016 |   6   |
| 09/01/2016 |   3   |
| 12/01/2016 |   3   |

^{*where the date values are stored as dates. If not, you can use =DateValue() to convert a date string to a date value.}

You'd should see a lot of data in the beginning followed by a lull of activity in the middle of the year.

Area, Line, Scatter, and Time Line (located in Other) should all do so:

By contrast, bar charts continue to treat date values as a simple string and return each of the values sequentially:

Bar Chart

And here's a public spreadsheet with all of the included examples

Move Sheets in Google Sheets Using Keyboard

This script will allow you to move the active sheet to the beginning or a pick sheet that needs to be moved.

Code

// global
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

// create menu entry in spreadsheet upon opening
function onOpen() {
  app.createMenu('Move Sheet')
    .addItem('Move Active', 'moveactiveSheet')
    .addItem('Move Sheet', 'movespecificSheet')
    .addToUi();  
}

// move active sheet to position zero
function moveactiveSheet() {
  ss.moveActiveSheet(0);  
}

// select sheet number (correct for zero-based array) and move to front
function movespecificSheet() {
  var int = app.prompt('Select Sheet', 'Use int. only', app.ButtonSet.OK_CANCEL)
    .getResponseText();
  ss.setActiveSheet(ss.getSheets()[int-1]);
  ss.moveActiveSheet(0);  
}

Example

I've created an example file for you: move sheets.
Make a copy of my file or add the code under Tools > Script editor. Press the bug button to authenticate the script.