how to insert multiple rows in a spreadsheet using google script

Question

Is there any way i can insert multiple Rows into Google SpreadSheet without using any loops.

Need to do this task using google Apps Script only.

function testMultipleEntry(){

  var sheet = SpreadsheetApp.openById(INSERT-SHEET-ID);
  var mainSpreadSheet= sheet.getActiveSheet();
  
  var fiveRows= mainSpreadSheet.getRange(2, 1, 5,mainSpreadSheet.getMaxColumns() );
  
  var tempSheet= SpreadsheetApp.create("TestSheetAppend").getActiveSheet();
  
 
  fiveRows.copyValuesToRange(tempSheet, 1, mainSpreadSheet.getMaxColumns(), 2, 5);//used the array[][]
}

last line is showing -- Service error: Spreadsheets

As max rows in the spreadsheet is more than 6K and this needs to be inserted into multiple spreadsheets so appending rows one by one is out of question as there is time out and the job goes unfinished. — mukul
– mukul, Commented Dec 10, 2014 at 14:53
Not to put too fine a point on it, but your code is confusing and inefficient, and makes it impossible to determine what it is you're trying to do. Are you trying to insert blank rows? Are you trying to grab data and place it in existing rows? The error you are getting is related to many things wrong with this code. for example, you can't use the line var tempSheet= SpreadsheetApp.create("TestSheetAppend").getActiveSheet(); for a few reasons, number 1 of which is it should be embedded in an 'if' statement to check if the sheet exists, otherwise the code will fail. — HDCerberus
– HDCerberus, Commented Dec 10, 2014 at 22:24
I need to avoid the loops because my original script is forcefully being terminated due to time out. I have one large sheet from there i need to get some chunk of rows then i need to put it in other spread sheets. And about this script which i have mentioned in my question is something which seems to copy multiple rows in a sheet in one go. — mukul
– mukul, Commented Dec 11, 2014 at 6:44
but that script doesn't work is not because of var tempSheet= SpreadsheetApp.create("TestSheetAppend").getActiveSheet(); lets consider this works fine. i have verified with logs — mukul
– mukul, Commented Dec 11, 2014 at 6:52

HDCerberus · Accepted Answer · 2015-10-09 17:42:20Z

3

I'm still not clear on what it is you're looking for, but this code takes data from one sheet, and inserts it into another one:

function enterData(){

  var ss = SpreadsheetApp.openById('SHEET-ID');
  var mainSheet= ss.getActiveSheet();
  var fiveRows= mainSheet.getRange(2, 1, 5,mainSheet.getMaxColumns());
  var createSheet= ss.insertSheet("TestSheetAppend");//This line will fail if a sheet by this name exists. 
  var tempSheet = ss.getSheetByName('TestSheetAppend');

  fiveRows.copyValuesToRange(tempSheet, 1, tempSheet.getMaxColumns(), 2, 5);//used the array[][]
}

If you're looking to send it to another sheet entirely, you can modify this code to do so.

edited Oct 9, 2015 at 17:42

answered Dec 11, 2014 at 15:52

HDCerberus

2,1634 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RollerCosta · Accepted Answer · 2020-08-06 16:49:13Z

0

I've spent some time looking into the official documentation on google app scripts, and based on that I can confirm you that there are many ways you can achieve this, one of which is explained below

getRange() + setValues()

Sample code

const startRowNumber = 1;
const starColumnNumber = 1;
const endRowNumber = dataToPut.length;
const endColumnNumber = dataToPut[0].length;

const range = sheet.getRange(startRowNumber, starColumnNumber, endRowNumber, endColumnNumber);
range.setValues(dataToPut);

answered Aug 6, 2020 at 16:49

RollerCosta

5,23410 gold badges57 silver badges71 bronze badges

Collectives™ on Stack Overflow

how to insert multiple rows in a spreadsheet using google script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related