how to insert multiple rows in a spreadsheet using google script

1
votes

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

2
javascriptgoogle-apps-script
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
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
Also, what is the reason to avoid for loops? – HDCerberus
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
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

2 Answers

3
votes

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.

0
votes

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);