Retting av #REF-feil i Google Sheets

Retting av #REF-feil i Google Sheets
Retting av #REF-feil i Google Sheets
Isanes Francois
Onsdag 17. april 2024 13:36:55

Forstå problemer med Google Sheets-vedlegg

Når du arbeider med Google Sheets, er en vanlig oppgave å sende arkdata som Excel-vedlegg via e-post. Denne prosessen kan strømlinjeformes ved hjelp av Google Apps Script, slik at brukere kan sende flere ark i en enkelt e-post. Det kan imidlertid oppstå problemer, for eksempel #REF-feilen, som vanligvis indikerer et referanseproblem i dataene som eksporteres.

Dette problemet viser seg ofte når Google Sheets bruker komplekse formler som QUERY(), som ikke oversettes godt når arkene konverteres til Excel-format. Feilen forstyrrer dataintegriteten i vedlegget, og utgjør en betydelig utfordring for brukere som stoler på disse eksportene for rapportering eller analyse.

Kommando Beskrivelse
SpreadsheetApp.getActiveSpreadsheet() Henter det aktive regnearket skriptet er bundet til.
spreadSheet.getSheetByName(sheet).getSheetId() Returnerer den unike identifikatoren for et ark i regnearket.
UrlFetchApp.fetch(url, params) Sender en forespørsel til en spesifisert URL ved hjelp av ulike parametere for å administrere HTTP-forespørselen.
Utilities.sleep(milliseconds) Setter kjøringen av skriptet på pause i et spesifisert antall millisekunder for å forhindre at API-hastighetsgrensene nås.
ScriptApp.getOAuthToken() Henter OAuth 2.0-tokenet for gjeldende bruker for å autentisere forespørsler.
getBlob() Henter dataene til en fil hentet fra en URL som en blob, som brukes til å legge ved filer til e-poster.

Skriptfunksjonalitet Forklaring

Skriptet som følger med er utformet for å automatisere prosessen med å sende flere Google Sheets som Excel-vedlegg i én enkelt e-post. Det begynner med å deklarere en rekke arknavn beregnet på eksport. Skriptet henter det aktive regnearket og itererer gjennom utvalget av arknavn for å generere nedlastings-URLer for hvert ark. Disse nettadressene er spesifikt formatert for å eksportere arkene som Excel-filer. Bruken av 'Utilities.sleep(10000);' er avgjørende her for å innføre en forsinkelse mellom hentingsforespørsler, som hjelper til med å administrere belastningen på Googles servere og forhindre at skriptet når frekvensgrensene.

Hver URL henter det respektive arket som en blob, som deretter blir navngitt i henhold til den forhåndsdefinerte filnavnematrisen. Dette trinnet er kritisk fordi det konverterer dataene fra arkene til et format som passer for e-postvedlegg. Etter å ha klargjort alle filblobbene, konstruerer skriptet et e-postobjekt med utpekte mottakere, en emnelinje og en brødtekstmelding. Blobbene er vedlagt denne e-posten, som deretter sendes med 'MailApp.sendEmail(melding);' kommando. Denne funksjonen er en del av Google Apps Scripts MailApp-tjeneste, som lar skript sende e-poster, invitasjoner og varsler.

Løser Google Sheets #REF-feil ved eksport

Google Apps Script-løsning

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();
  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return \`https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx&gid=${sheetId}\`;
  });
  var reportName = spreadSheet.getSheetByName('IMEIS').getRange(1, 14).getValue();
  var params = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var fileNames = ['OH INV - B2B.xlsx', 'OH INV - Acc.xlsx', 'OH INV - B2C.xlsx', 'B2B.xlsx', 'ACC.xlsx', 'B2C.xlsx'];
  var blobs = urls.map((url, index) => {
    Utilities.sleep(10000);  // Delay added to avoid hitting rate limits
    var response = UrlFetchApp.fetch(url, params);
    return response.getBlob().setName(fileNames[index]);
  });
  var message = {
    to: 'email@domain.com',
    cc: 'email@domain.com',
    subject: 'Combined REPORTS - ' + reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }
  MailApp.sendEmail(message);
}

Avansert innsikt i eksportproblemer med Google Regneark

Eksport av data fra Google Sheets til Excel-formater ved hjelp av skript kan avdekke underliggende kompleksitet i databehandling, spesielt når du bruker avanserte funksjoner som QUERY(). #REF-feilen som oppstår i slike eksporter indikerer vanligvis uløste referanser i Excel-miljøet, som ikke forekommer i selve Google Sheets. Denne forskjellen oppstår ofte fordi noen av funksjonene i Google Sheets, som visse QUERY()-operasjoner eller egendefinerte skript, ikke støttes eller oppfører seg annerledes i Excel.

Dette problemet understreker viktigheten av å sikre kompatibilitet mellom Google Sheets-formler og Excels håndtering av formler og dataspørringer. Utviklere må ofte implementere tilleggskontroller eller alternative metoder for å sikre dataintegritet når de går fra Googles miljø til Microsofts, spesielt ved automatisering av prosesser som e-postvedlegg av regnearkdata.

Vanlige spørsmål om Google Sheets-skripting

  1. Spørsmål: Hvorfor vises #REF-feilen når du eksporterer fra Google Sheets til Excel?
  2. Svar: #REF-feilen oppstår vanligvis fordi visse referanser eller formler i Google Sheets ikke gjenkjennes eller er inkompatible med Excels formelmiljø.
  3. Spørsmål: Hvordan kan jeg forhindre treffgrenser med Google Apps Scripts?
  4. Svar: Implementering av pauser i skriptet ved å bruke Utilities.sleep(milliseconds) kan bidra til å administrere frekvensen av forespørsler og unngå å overskride Googles takstgrenser.
  5. Spørsmål: Hva gjør muteHttpExceptions i et URL-hentingskall?
  6. Svar: Det lar skriptet fortsette kjøringen uten å gi et unntak hvis HTTP-forespørselen mislykkes, nyttig for å håndtere feil på en elegant måte.
  7. Spørsmål: Kan jeg tilpasse filnavnet til hvert ark når jeg eksporterer til Excel?
  8. Svar: Ja, du kan angi egendefinerte navn for hver blob konvertert fra arket før du legger dem ved en e-post, som vist i skriptet.
  9. Spørsmål: Er det en måte å eksportere Google Sheets direkte til Excel uten mellomliggende skript?
  10. Svar: Ja, du kan manuelt laste ned et Google-ark i Excel-format direkte fra Fil-menyen i Google Sheets, men automatisering av dette krever skripting.

Endelig innsikt i arkeksportutfordringer

Gjennom denne utforskningen blir det tydelig at mens Google Apps Script gir kraftige verktøy for å automatisere og forbedre funksjonene i Google Sheets, oppstår visse kompleksiteter når man bruker grensesnitt med forskjellige plattformer som Excel. #REF-feilene er en vanlig fallgruve, spesielt når man arbeider med komplekse søk og datareferanser som ikke oversetter godt utenfor Googles økosystem. Å forstå disse begrensningene og planlegge for dem i skript kan redusere forekomsten av slike problemer betydelig, noe som fører til jevnere databehandlingsprosesser.