javascript - How to solve Exception: Service error: Drive - Stack Overflow

admin管理员组

文章数量:1401491

I have this code that can copy and edit spreadsheet in the same parent folder. A few months ago it still work but now i get this error message

Exception: Service error: Drive.

Here's the code

var outlet = [
["p1", "place1", "great place 1"], 
["p2", "place2", "great place 2"],
];

function looping() {
    for (var i = 0; i < outlet.length; i++) {
      duplicate(outlet[i][2], i);
      Logger.log(outlet[i][1]);
    }
}

function duplicate(nama, i) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssid = ss.getId();
  const ssdrive = DriveApp.getFileById(ssid);
  const parent = ssdrive.getParents().next();

  const newss = ssdrive.makeCopy(nama, parent);
  const newurl = newss.getUrl();

  modify(newurl, i);
  debugger;
}

function modify(url, i) {
  const ssopen = SpreadsheetApp.openByUrl(url);
  const range1 = ssopen.getRange("A1");
  const range2 = ssopen.getRange("A2");
  const toko = ssopen.getDataRange();
  const real = outlet[i][1].replace("SPOKE SMG ","").replace(" ","")

  range1.setValue(outlet[i][0]);
  range2.setValue(outlet[i][1]);
  ssopen.setNamedRange(real, toko);
  Logger.log(outlet[i][0]);
  debugger;
}

sorry for the messy language

I have this code that can copy and edit spreadsheet in the same parent folder. A few months ago it still work but now i get this error message

Exception: Service error: Drive.

Here's the code

var outlet = [
["p1", "place1", "great place 1"], 
["p2", "place2", "great place 2"],
];

function looping() {
    for (var i = 0; i < outlet.length; i++) {
      duplicate(outlet[i][2], i);
      Logger.log(outlet[i][1]);
    }
}

function duplicate(nama, i) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssid = ss.getId();
  const ssdrive = DriveApp.getFileById(ssid);
  const parent = ssdrive.getParents().next();

  const newss = ssdrive.makeCopy(nama, parent);
  const newurl = newss.getUrl();

  modify(newurl, i);
  debugger;
}

function modify(url, i) {
  const ssopen = SpreadsheetApp.openByUrl(url);
  const range1 = ssopen.getRange("A1");
  const range2 = ssopen.getRange("A2");
  const toko = ssopen.getDataRange();
  const real = outlet[i][1].replace("SPOKE SMG ","").replace(" ","")

  range1.setValue(outlet[i][0]);
  range2.setValue(outlet[i][1]);
  ssopen.setNamedRange(real, toko);
  Logger.log(outlet[i][0]);
  debugger;
}

sorry for the messy language

• javascript
• google-sheets
• google-apps-script
• google-drive-api

Share Improve this question Follow edited Mar 25 at 14:14 Wicket 38.7k99 gold badges8080 silver badges194194 bronze badges asked Mar 25 at 7:36 Trem TremTrem Trem 111 bronze badge 2

• 2 You need to debug. Try one iteration, if it works, then find if you have some limits. Wrap the loop in a try catch and examine the complete error message – mplungjan Commented Mar 25 at 7:46
• Which line does this error be referring to? – Babanana Commented Mar 25 at 8:19

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

For some reason, beyond the control of Google Drive end-users, sometimes the files created using DriveApp take longer than usual. It might be possible that your previous executions were fast enough, but lately, they weren't.

A "quick and dirty fix" is to add Utilities.sleep(milliseconds); before calling modify(newurl, i);. Start assigning 10000, 10 seconds, as the Utilities.sleep parameter. If this works, you might keep it or try doing a trial and error to find an optimal value.

A better solution could be using an algorithm like exponential backoff to handle a specific number of retries, each with a longer sleep time.

---

I have added an adaptation of the exponential backoff function taken from peterherrman's Gist and applied a tweak to reduce the calls to the SpreadsheetApp methods.

var outlet = [
  ["p1", "place1", "great place 1"],
  ["p2", "place2", "great place 2"],
];

function looping() {
  /**
   * Moved the next two statements from the duplicate function
   * here to reduce the number of calls of SpreadsheetApp methods.
   */
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssid = ss.getId();
  
  for (var i = 0; i < outlet.length; i++) {
    /**
     * call is the function that handles the exponential backoff algorithm. 
     */
    call(duplicate, [outlet[i][2], i]);
    Logger.log(outlet[i][1]);
  }

  /**
   * Moved this function inside the looping function code block
   */
  function duplicate(nama, i) {

    const ssdrive = DriveApp.getFileById(ssid);
    const parent = ssdrive.getParents().next();

    const newss = ssdrive.makeCopy(nama, parent);
    const newurl = newss.getUrl();

    modify(newurl, i);
    debugger;
  }
}

function modify(url, i) {
  const ssopen = SpreadsheetApp.openByUrl(url);
  const range1 = ssopen.getRange("A1");
  const range2 = ssopen.getRange("A2");
  const toko = ssopen.getDataRange();
  const real = outlet[i][1].replace("SPOKE SMG ", "").replace(" ", "")

  range1.setValue(outlet[i][0]);
  range2.setValue(outlet[i][1]);
  ssopen.setNamedRange(real, toko);
  Logger.log(outlet[i][0]);
  debugger;
}

/**
  * Adaptation of peterherrman's gist. The link is the references section.
  */
function call(func, args, optLoggerFunction) {
  for (var n = 0; n < 6; n++) {
    try {
      return func(...args);
    } catch (e) {
      if (optLoggerFunction) { optLoggerFunction("GASRetry " + n + ": " + e) }
      if (n == 5) {
        throw e;
      }
      Utilities.sleep((Math.pow(2, n) * 1000) + (Math.round(Math.random() * 1000)));
    }
  }
}

---

Related

• Why did I suddenly get "You've exceeded the Rate Limit" error?
• peterherrman's Exponential Backoff

本文标签： javascriptHow to solve Exception Service error DriveStack Overflow