Apps Script - Creating a Spreadsheet then copy all the value and paste it in a different spreadsheet-CodePudding

I'm trying to to do the following tasks using Apps Script:

Create a new spreadsheet. The name will be "My spreadsheet" Date of the day
Write a random value in the new spreadsheet
Copy the entire sheet (1st tab) from the new spreadsheet
Paste the value in another spreadsheet (in a specific sheet)

Here is the script I've written so far:

function copyPasteAllData() {
  var date = Utilities.formatDate(new Date(), "GMT 7", "dd/MM/yyyy");
  // get today's date
  var ss = SpreadsheetApp.create("Existing Data - Apps Script - "  date);
  // create a new spreadsheet
  var ssId = ss.getId();
  // get ID of new spreadsheet
  var existing = Sheets.Spreadsheets.Values.get('MyspreadsheetID', "Existing Data");
  // get existing Spreadsheet   sheet location
  var newSheet = Sheets.Spreadsheets.Values.get(ssId, "Sheet1");
  // get new Spreadsheet   sheet location
  ss.getRange('A1').setValue('1')
  // setup a random value
  var rangeAllData = newSheet.getRange(1, 1, newSheet.getMaxRows(), newSheet.getMaxColumns());
  // copy the entire sheet from the 
  existing.rangeAllData.setValue()
  }

I think the line below need to be changed but I'm not sure how to fix this. I'm still quite a beginner with Apps Script and coding in general, apologies if it seems obvious.

var existing = Sheets.Spreadsheets.Values.get('MyspreadsheetID', "Existing Data"); 
var newSheet = Sheets.Spreadsheets.Values.get(ssId, "Sheet1");

Feel free to change anything in the script. Just note that I can only get the sheet ID and not the name

Thank you

CodePudding user response：

Updated from the comment below:

In this case you can use the method getDataRange() to get the data from the new Sheet.

Then on the destination you can use the same range from the new sheet or use the one you want from the old sheet:

function copyPasteAllData() {

  //getDate
  var date = Utilities.formatDate(new Date(), "GMT 7", "dd/MM/yyyy");

  //Create new sheet   set value
  var newSheet = SpreadsheetApp.create("Existing Data - Apps Script - "   date);
  newSheet.getRange('A1').setValue('this is new');

  //get new sheet DataRange
  var source = newSheet.getId();
  var newSheetDataRange = SpreadsheetApp.openById(source).getSheets()[0].getDataRange().getA1Notation();
  Logger.log(newSheetDataRange)
  var newSheetValues = SpreadsheetApp.openById(source).getSheets()[0].getDataRange().getValues();
  Logger.log(newSheetValues)

  //copy the data to the old sheet
  SpreadsheetApp.openById('OLD SHEET ID').getSheets()[0].getRange(newSheetDataRange).setValues(newSheetValues);
}