-1

I am trying to get data of 2 columns (B, C) from one spreadsheet and paste it into another spreadsheet. It was working perfectly before yesterday. But for the past 2 days the script is running out of time and crashing without copying values. Sometimes it runs successfully but only gets the first 1000 rows of a total of 12000 rows of data. Here is the script:

function importRangeV1(sourceURL, sourceRange, destinationURL, sheetName){

  var sourceSS = SpreadsheetApp.openByUrl(sourceURL);
  var sourceRng = sourceSS.getRange(sourceRange)
  var sourceVals = sourceRng.getValues();

 
   SpreadsheetApp.flush();
  var destinationSS = SpreadsheetApp.openByUrl(destinationURL);
  var destSheet = destinationSS.getSheetByName(sheetName);
  destSheet.clearContents();

  var destRange = destSheet.getRange(1,1,sourceVals.length,sourceVals[0].length);
  destRange.setValues(sourceVals);  
}

Now the following 2 statements in the above code are similar in terms of working:

   var sourceSS = SpreadsheetApp.openByUrl(sourceURL);
   var destinationSS = SpreadsheetApp.openByUrl(destinationURL);

However, the issue arises in this statement:

var destinationSS = SpreadsheetApp.openByUrl(destinationURL);

This statement takes so much time that the script throws the following error:

“Service Spreadsheets timed out while accessing spreadsheet with id [spreadsheet id here]”

Here is the link to the destinationURL: https://docs.google.com/spreadsheets/d/1T-vbfHdCNqULCY4voyxdPhR0v_EIxTsQZszfn9mWJ9Y/edit?usp=sharing

Here is how I run this script in the sheet:

enter image description here

I found this question somewhat similar to my issue but it does not address openByUrl() or openById(), any help in this regard would be much appreciated.

  • I have to apologize for my poor English skill. Unfortunately, I cannot understand the relationship between `But for the past 2 days the script is running out of time and crashing without copying values. Sometimes it runs successfully but only gets the first 1000 rows of a total of 12000 rows of data.` and your showing script. Can I ask you about the detail of it? – Tanaike Jan 24 '23 at 06:37
  • yes, I meant to say that the script is starting to crash recently, it keeps running and after copying 1000 rows of data to destination sheet, it crashes, the source sheet has around 12000 rows of data but script copies only 1000 rows to destination sheet. –  Jan 24 '23 at 06:45
  • Thank you for replying. I apologize for my poor English skill again. Unfortunately, from your reply, I cannot still understand the relationship between `But for the past 2 days the script is running out of time and crashing without copying values. Sometimes it runs successfully but only gets the first 1000 rows of a total of 12000 rows of data.` and your showing script. For example, how did you run your function `importRangeV1`? – Tanaike Jan 24 '23 at 06:47
  • I am showing the script that I use to copy column B,C from source spreadsheet to destination spreadsheet, I run script by button in the sheet, the function gets `sourceRange,sourceUrl, destinationUrl, and destinationSheet` as an input. –  Jan 24 '23 at 06:51
  • Thank you for replying. From your reply, you run your script `importRangeV1` by clicking a button on the Spreadsheet. Is my understanding correct? – Tanaike Jan 24 '23 at 07:02
  • yes. I run script using a button –  Jan 24 '23 at 07:03
  • Thank you for replying. From `yes. I run script using a button`. in the current stage, the arguments cannot be given to the assigned function of the button. So, I'm worried that you might have miscopied your script. How about this? – Tanaike Jan 24 '23 at 07:04
  • In the sheet cells, `A1: SourceRange, B1: SourceURL, C1: DestinationUrl, D1: DestinationSheetName` I pass all these cells to function like: `importRangeV1(A1,B1,C1,D1)` It was always working like this correctly. –  Jan 24 '23 at 07:09
  • Thank you for replying. From `In the sheet cells, A1: SourceRange, B1: SourceURL, C1: DestinationUrl, D1: DestinationSheetName I pass all these cells to function like: importRangeV1(A1,B1,C1,D1) It was always working like this correctly.`, can I ask you about the detailed method for running your script `importRangeV1`? I deeply apologize that I am trying to correctly understand your question. – Tanaike Jan 24 '23 at 07:11
  • Yes, I have added a screenshot at the end of my question to explain how this script runs, All 4 cells in the row are used as input to the function. –  Jan 24 '23 at 07:14
  • Thank you for replying and adding a sample image. But, unfortunately, I cannot still understand the method for running the script. I think that this is due to my very poor English skill. I deeply apologize for my very poor English skill again. In order to correctly understand it, for example, can you provide the sample Spreadsheet for correctly replicating your current issue and confirming the method for running the script? – Tanaike Jan 24 '23 at 07:17

1 Answers1

0

It seems your sheet is heavily loaded with formulas or data which is the main cause of this issue, I had the same issue with one of my sheets so how about this one for a solution:

    function importRangeV1(sourceURL, sourceRange, destinationURL, sheetName){
       
     var sourceId = sourceURL.match(/\/d\/(.+)\//)[1];      //extract id from url
     var destId =   destinationURL.match(/\/d\/(.+)\//)[1]; //extract id from url
    
     var any = {};
     var srcValues = Sheets.Spreadsheets.Values.get(sourceId, sourceRange).values;
     Sheets.Spreadsheets.Values.clear(any,destId,sheetName+"!A1:B");
     Sheets.Spreadsheets.Values.update({values: srcValues}, destId, sheetName, {valueInputOption: "USER_ENTERED"});
};

This script uses Sheets API instead of openByUrl() to access the required sheet and put the data in the specified range. Test it and let me know.

EagleEye
  • 240
  • 2
  • 8