CODEWITHSUNDEEP
javascriptgoogle-apps-scriptspreadsheetgoogle-drive-api
Yassir D.
Yassir D.

Reputation: 3

Copy sheet to another spreadsheet [Google Apps Script]

I can't get this function working. It's has to copy all the values of spreadsheet "A" to spreadsheet "B".

function triggerOnTime() {
  var SpreadSheetKeyA = "142WNsfQQkSx4BuNhskDM9aXD9ylRHNZh34oO5TBTt1g";
  var SpreadSheetKeyB = "1h8fDwCUUPHRdmTHu-5gMyqU294ENZxCZcHCNCuN6r_Y";
  var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getActiveSheet();
  var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getActiveSheet();
  var data = sheet1.getDataRange().getValues();
  var array = [];
  for (var y = 1; y < data.length; y++) {

    for (var x = 0; x < 35; x++){
      array.push(data[y][x]);
    }
      Logger.log(array);
      sheet2.appendRow(array);
      sheet1.deleteRow(y);
      }
}

It now copy's two or three values, but copy them multiple times (random). The function must also delete the copied values from sheetA. But it only deletes the values that are added to sheetB.

EDIT (Updated code)

function triggerOnTime() {
  var SpreadSheetKeyA = "142WNsfQQkSx4BuNhskDM9aXD9ylRHNZh34oO5TBTt1g";
  var SpreadSheetKeyB = "1h8fDwCUUPHRdmTHu-5gMyqU294ENZxCZcHCNCuN6r_Y";
  var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getActiveSheet();
  var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getActiveSheet();
  var data = sheet1.getDataRange().getValues();
  var array = [];
  for (var y = 0; y < data.length; y++) {

    for (var x = 0; x < 35; x++){
      array.push(data[y][x]);
    }
      sheet2.appendRow(array);
      sheet1.deleteRow(y+1);
      array = [];
      }
}

Upvotes: 0

Views: 949

Answers (1)

CameronC
CameronC

Reputation: 110

After playing around a little, I have found a fix for you (albeit a hack, I guess. Im sure there's a better way of doing it)

Whats happening is, on line 14 where you appendRow(array), you are appending the array at it's current state to the second sheet; basically creating a big pyramid of values of your array over time.

If sheet 1 contained something like this: 1,1,1

2,2,2

3,3,3

4,4,4

5,5,5

Your sheet 2 would get this appended: 1,1,1

1,1,1,2,2,2

1,1,1,2,2,2,3,3,3

... and so on.

You could do something like this:

function triggerOnTime() {
  var SpreadSheetKeyA = "142WNsfQQkSx4BuNhskDM9aXD9ylRHNZh34oO5TBTt1g";
  var SpreadSheetKeyB = "1h8fDwCUUPHRdmTHu-5gMyqU294ENZxCZcHCNCuN6r_Y";
  var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getActiveSheet();
  var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getActiveSheet();
  var data = sheet1.getDataRange().getValues();
  var array = [];
  for (var y = 1; y < data.length; y++) {

    for (var x = 0; x < 35; x++){
      array.push(data[y][x]);
    }
      sheet2.appendRow(array);
      sheet1.deleteRow(y);
      array = []; //reset the array contents
      }
}

The additional line will just reset the array and add the row you want to copy across.

Let me know if this works out for you.

Upvotes: 1

Related Questions