1

I need to copy data from one spreadsheet to another every day. There is 13 columns and about 50k rows (counts of rows is growing with time). But from time to time, after copy some data, function is stoped and I got Exception

"service spreadsheets timed out while accessing document with id".

Where is the problem ? Do my function needs to optimise ?

function copyAll4() {
  let tss = SpreadsheetApp.getActiveSpreadsheet(); 
  let ts = tss.getSheetByName('allOP');
  let ui = SpreadsheetApp.getUi();
  let dajurl = ui.prompt('paste source data url',
      '',
      ui.ButtonSet.OK_CANCEL); 
  let urldozrodla= dajurl.getResponseText();
  
  let sss = SpreadsheetApp.openByUrl(urldozrodla); 
  let ss = sss.getSheetByName('Sheet'); 
  let nrows_ss = ss.getMaxRows();
  
  let nrows_ts = ts.getMaxRows();
  ts.deleteRows(3, nrows_ts-2);
  ts.insertRowsAfter(2, nrows_ss-2);
    
  let SRange2 = ss.getRange("Sheet!B:C");
  let A1Range2 = SRange2.getA1Notation();
  let SData2 = SRange2.getValues();
  ts.getRange(A1Range2).setValues(SData2);
    
  let SRange9 = ss.getRange("Sheet!E:E");
  let A1Range9 = SRange9.getA1Notation();
  let SData9 = SRange9.getValues();
  ts.getRange(A1Range9).setValues(SData9);
    
  let SRange6 = ss.getRange("Sheet!G:K");
  let A1Range6 = SRange6.getA1Notation();
  let SData6 = SRange6.getValues();
  ts.getRange(A1Range6).setValues(SData6);
    
  let SRange10 = ss.getRange("Sheet!M:P");
  let A1Range10 = SRange10.getA1Notation();
  let SData10 = SRange10.getValues();
  ts.getRange(A1Range10).setValues(SData10);
    
  let SRange11 = ss.getRange("Sheet!V:V");
  let A1Range11 = SRange11.getA1Notation();
  let SData11 = SRange11.getValues();
  ts.getRange(A1Range11).setValues(SData11);
  
  
  let currentDate = new Date();
  ts.getRange("A1").setValue(currentDate);  
}

When I try to use copyTo I got Exception: The target range and the source range must be in the same spreadsheet.

function copy_with_CopyTo() {
  let targetSheet = SpreadsheetApp.getActiveSpreadsheet(); 
  let targetSheetTab = targetSheet.getSheetByName('allOP');
  let ui = SpreadsheetApp.getUi();
  let uiPaste = ui.prompt('paste source data url',
      '',
      ui.ButtonSet.OK_CANCEL);
  let urlSource= uiPaste.getResponseText();
  
  let sourcetSheet = SpreadsheetApp.openByUrl(urlSource); 
  let sourcetSheetTab = sourcetSheet.getSheetByName('Sheet'); 
  let sourcetSheetTabRows = sourcetSheetTab.getMaxRows();
  let targetSheetTabRows = targetSheetTab.getMaxRows();
      targetSheetTab.deleteRows(3, targetSheetTabRows-2);
      targetSheetTab.insertRowsAfter(2, sourcetSheetTabRows-2);
      
  let sourceRange = sourcetSheetTab.getDataRange();
      sourceRange.copyTo(targetSheetTab.getRange(1,1));
}
7
  • Welcome! As far as I understand your code, you want to copy the entire B:V range, but skip the D,F,L and Q:U columns. In this case, you have to copy and paste the values five times into the array and into the target range (of course, this is a long time). Have you thought about copying B:V in one go with copyTo(), as shown in this article, and then clearing the extra columns with clear()?
    – JohnSUN
    Nov 14, 2022 at 9:32
  • Hello @JohnSUN , the data is copying from one sheet to another sheet which is located in a different document. I have to use getValues not copyTo. Nov 15, 2022 at 12:11
  • 1
    The title doesn't match bt he error mentioned in the question body. Please add the textual error message.
    – Rubén
    Nov 15, 2022 at 20:22
  • Hi, welcome to WebApps. commands like getValues and setValues are known to be time costly, and your code several of each. There might be several ways that you code could be made more efficient. Leavug aside the copyTo vs get/setValues, would you please explain the reasons why don't you copy all the columns from the source?
    – Tedinoz
    Nov 17, 2022 at 22:38
  • Hi @Tedinoz, copying all the columns means to copy 21 rows with 50k columns. It is almost two times more data. I have tested formula with one getValues and setValues and It took much more time and the error occurred more frequently. Nov 18, 2022 at 9:16

1 Answer 1

1
function copy_with_CopyTo() {
  let targetSheet = SpreadsheetApp.getActiveSpreadsheet(); 
  let targetSheetTab = targetSheet.getSheetByName('allOP');
  let ui = SpreadsheetApp.getUi();
  let uiPaste = ui.prompt('paste source data url',
      '',
      ui.ButtonSet.OK_CANCEL);
  let urlSource= uiPaste.getResponseText();
  let sourcetSheet = SpreadsheetApp.openByUrl(urlSource); 
  let sourcetSheetTab = sourcetSheet.getSheetByName('Sheet'); 
  sourcetSheetTab.copyTo(targetSheet)
  // Logger.log("DEBUG: data copy complete")
}

copyTo(spreadsheet) Dec ref is a SHEET method. You are copying the entire sheet from one spreadsheet to another. No need to insert or delete rows.

The new sheet will be named Copy of Sheet, and if that sheet name already exists in the target spreadsheet, then the content will be overwritten by copyTo.

Once the data has been copied, you can do any other housekeeping jobs, such as:

  • rename the sheet (having checked for the existence of any existing name

// check if target sheet exists.
  // if yes, delete and create
  // if no, create
  let targetSheetname = "allOP"
  let tst = targetSheet.getSheetByName(targetSheetTab)
  if (!tst) {
    targetSheet.insertSheet(targetSheetname)
    // Logger.log("DEBUG: temp sheet doesn't exist so clean copy created")
  }
  else{
    targetSheet.deleteSheet(tst)
    targetSheet.insertSheet(targetSheetname)
    //Logger.log("DEBUG: target sheet exists so deleted and clean copy created")
  }

  • Perhaps the header rows in the target are different than the source, but you could have a template in the target containing just the header rows and copy them to the data sheet
  • if columns "A","D", etc are not required use, you could use the "Range.clear" method to delete the contents of those columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.