How to import a sheet from another Google Spreadsheet using Google Apps Scipt?

user_18139009 0
Viewed 188

I want to import a sheet from another Google Spreadsheet in place of values only using Google Apps Script, and check if the existing sheet is there or not. Any help is highly appreciated.

  function CopyDataToNewFile() {
  var sss = SpreadsheetApp.openById('0AjN7uZG....'); // sss = source spreadsheet
  var ss = sss.getSheetByName('Monthly'); // ss = source sheet
  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('8AjN7u....'); // tss = target spreadsheet
  var ts = tss.getSheetByName('RAWData'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);
};
google-apps-script google-sheets
1 Answers

Try this

/* Global configuration */
const CONFIG = {
  URL: {
    /* Enter the source sheet url between '' */
    SOUCE_SHEET_URL: '',
  },
  SHEET_TO_COPY: {
    /* Enter the source sheet name between '' */
    SHEET_NAME: '',
  },
  SPREADSHEET: {
    ACTIVE_SPREADSHEET: SpreadsheetApp.getActiveSpreadsheet(),
  },
  TOAST: {
    T1: 'Sheet found, deleting the current version.',
    T2: 'Sheet not found, copying the new sheet.',
    T3: 'Sheet copied successfully.',
    T4: 'Enter the correct url and sheet name.',
  }
};

const importSheet = () => {
  try {
    const sourceSheet = SpreadsheetApp.openByUrl(CONFIG.URL.SOUCE_SHEET_URL).getSheetByName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    /* Before copying the sheet, delete the exiting copy (if any) */
    const existingSheet = CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.getSheetByName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    if (existingSheet) {
      SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T1, 'Status', 3);
      Utilities.sleep(2000);
      CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.deleteSheet(existingSheet);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T2, 'Status', 3);
      Utilities.sleep(2000);
    }
    SpreadsheetApp.flush();
    const destinationSheet = sourceSheet.copyTo(CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET);
    destinationSheet.setName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.setActiveSheet(destinationSheet);
    SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T3, 'Success ', 3);
  }
  catch (err) {
    SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T4, 'Failed ', 3);
  }
};
user_111157680
Related