Google Apps Scripts #GoogleAppScript #GAS

Add Files to Multiple Folders with Code.gs

 function organizeFolders() {
  // Parent Folders
  var parentA = DriveApp.createFolder('Dad');
  var parentB = DriveApp.createFolder('Mom');

  // Child folder inside Parent Folder A
  var child = parentA.createFolder('Child');

  // Place Child Folder inside another Parent Folder B
  parentB.addFolder(child);
}

Clear Content.gs

 clearContent() 
Clears the content of the range, leaving the formatting intact.


var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange("A1:D10");
range.clearContent();

Convert Sheets to Another File Type.gs

 function getFileAsBlob(exportUrl) {
 let response = UrlFetchApp.fetch(exportUrl, {
     muteHttpExceptions: true,
     headers: {
       Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
     },
   });
 return response.getBlob();
}




function testExportSheetAsPDF() {
 let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
 Logger.log("Content type: " + blob.getContentType());
 Logger.log("File size in MB: " + blob.getBytes().length / 1000000);
}




let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");

blob.setName("Monthly sales report");


// SAVE TO DRIVE

function exportSheetAsPDFToDrive() {
  let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
  let file = DriveApp.createFile(blob);
  Logger.log(file.getUrl());
}

// SEND AS EMAIL ATTACHMENT



function sendExportedSheetAsPDFAttachment() {
  let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");

  var message = {
    to: "[email protected]",
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [blob.setName("Monthly sales report")]
  }

  MailApp.sendEmail(message);
}

Copy Formula Down.gs

 //https://googlescripts.harryonline.net/copy-formulas-down
 
 
 function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Copy formulas",
    functionName : "copyFormulas"
  }];
  sheet.addMenu("Scripts", entries);
};
function copyFormulas() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var row = 2;
    CopyFormulasDown.copyFormulasDown(sheet, row);
}

Copy Sheet to Another.gs

 // Copy Sheet
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheet = source.getSheets()[0];

var destination = SpreadsheetApp.openById('ID_GOES HERE');
sheet.copyTo(destination);

Copy Specific Values from Sheet to Another.gs

 var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var source = ss.getSheets()[0];
var destination = ss.getSheets()[1]; 
destination.clear() 

var range = source.getRange(1,1,10, 5);  
range.copyValuesToRange(destination, 1, 5, 1, 10);

Copy Table Data and Format to Destination.gs

 CopyTable("sda12wrsdsdaada121", "sheetA", "sheetB", "A1:D200")
 
 
 /**
 * Copy data and formatting from source to destination
 * @param {string} srcId id of source
 * @param {string} srcSheetName sheet's name of source
 * @param {string} destSheetName sheet's name of destination
 * @param {string} copyRange A1Notation of range
 */
function CopyTable(srcId, srcSheetName, destSheetName, copyRange){

  var srcSpreadSheet = SpreadsheetApp.openById(srcId);
  var srcSheet = srcSpreadSheet.getSheetByName(srcSheetName);

  var destSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var destSheet = destSpreadSheet.getSheetByName(destSheetName);
  destSheet.clear();

  var srcRange = srcSheet.getRange(copyRange); 

  var values = srcRange.getValues();
  var background = srcRange.getBackgrounds();
  var fontColor = srcRange.getFontColors();
  var fontFamily = srcRange.getFontFamilies();
  var fontLine = srcRange.getFontLines();
  var fontSize = srcRange.getFontSizes();
  var fontStyle = srcRange.getFontStyles();
  var fontWeight = srcRange.getFontWeights();
  var textStyle = srcRange.getTextStyles();
  var horAlign = srcRange.getHorizontalAlignments();
  var vertAlign = srcRange.getVerticalAlignments();
  var bandings = srcRange.getBandings();
  var mergedRanges = srcRange.getMergedRanges();

  var destRange = destSheet.getRange(copyRange);
 
  destRange.setValues(values);
  destRange.setBackgrounds(background);
  destRange.setFontColors(fontColor);
  destRange.setFontFamilies(fontFamily);
  destRange.setFontLines(fontLine);
  destRange.setFontSizes(fontSize);
  destRange.setFontStyles(fontStyle);
  destRange.setFontWeights(fontWeight);
  destRange.setTextStyles(textStyle);
  destRange.setHorizontalAlignments(horAlign);
  destRange.setVerticalAlignments(vertAlign);
  
  for (let i in bandings){
    let srcBandA1 = bandings[i].getRange().getA1Notation();
    let destBandRange = destSheet.getRange(srcBandA1);

    destBandRange.applyRowBanding()
    .setFirstRowColor(bandings[i].getFirstRowColor())
    .setSecondRowColor(bandings[i].getSecondRowColor())
    .setHeaderRowColor(bandings[i].getHeaderRowColor())
    .setFooterRowColor(bandings[i].getFooterRowColor());
  }

  for (let i = 0; i < mergedRanges.length; i++) {
    destSheet.getRange(mergedRanges[i].getA1Notation()).merge();
  }
 
  for (let i = 1; i <= srcRange.getHeight(); i++) {
    let width = srcSheet.getColumnWidth(i);
    destSheet.setColumnWidth(i, width);
  }
 
  for (let i = 1; i <= srcRange.getWidth(); i++){
    let height = srcSheet.getRowHeight(i);
    destSheet.setRowHeight(i, height);
  }
}

Copy Text Formatting from Range.gs

  /**
     copying full formatting including sizez and merging from one range to new location
https://stackoverflow.com/questions/25106580/copy-value-and-format-from-a-sheet-to-a-new-google-spreadsheet-document
below first coordinates of original range we want to copy and then cooridinaes of the begining of the place ino which we want to copy our range
    @param startColumnOfOriginal {Number} 
     @param startRowOfOriginal {Number} 
     @param numberOfRows {Number} 
     @param numberOfColumns {Number} 
     
     @param startColumnOfTarget {Number} 
     @param startRowOfTarget {Number} 
   
     @param sheetOfOrigin {Sheet} sheet object of where our source is
     @param sheetOfTarget {Sheet} sheet object where we want to copy it
     */
     
     function copyFullFormatting(startRowOfOriginal,startColumnOfOriginal,numberOfRows
     ,numberOfColumns, startRowOfTarget, startColumnOfTarget,  sheetOfOrigin, sheetOfTarget
     ){
     const sourceRange = sheetOfOrigin.getRange(
     startRowOfOriginal, startColumnOfOriginal, numberOfRows, numberOfColumns)
     
     const targetRange = sheetOfTarget.getRange(
     startRowOfTarget, startColumnOfTarget, numberOfRows, numberOfColumns)
     sourceRange.copyFormatToRange(sheetOfTarget,startColumnOfOriginal, startColumnOfTarget+ numberOfColumns, startRowOfTarget, startRowOfTarget+numberOfRows )
     //iterating over rows of source range
     for(var rowNumb=startRowOfOriginal;rowNumb<startRowOfOriginal+numberOfRows;rowNumb++  ){
     const targetRowNumb = rowNumb-startRowOfOriginal+startRowOfTarget
     sheetOfTarget.setRowHeight(targetRowNumb, sheetOfOrigin.getRowHeight(rowNumb))  
     }
     
     // iterating over columns in target range
     for (var colNumb=startColumnOfOriginal;colNumb<startColumnOfOriginal+numberOfColumns;colNumb++  ){
     const targetColNumb = colNumb-startColumnOfOriginal+startColumnOfTarget
     sheetOfTarget.setColumnWidth(targetColNumb, sheetOfOrigin.getColumnWidth(colNumb))

     }
     
     
     
     }
// Get the sheet:
const sheet = SpreadsheetApp.getActiveSheet();

// Get all values:
const sheetValues = shee t.getDataRange().getValues();

// Get the value of the 1st cell:
const cell = sheetValues[0][0];

// Set a value of the 1st cell:
sheet.getRange(1, 1).setValue(‘new value’);

 


https://dev.to/jennieji/i-find-time-from-automation-schedule-a-team-sharing-with-google-apps-script-17b2

Covert Excel to Sheets.gs

function convertExcelToGoogleSheets(fileName) {
  let files = DriveApp.getFilesByName(fileName);
  let excelFile = null;
  if(files.hasNext())
    excelFile = files.next();
  else
    return null;
  let blob = excelFile.getBlob();
  let config = {
    title: "[Google Sheets] " + excelFile.getName(),
    parents: [{id: excelFile.getParents().next().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let spreadsheet = Drive.Files.insert(config, blob);
  return spreadsheet.id;
}

find_and_replace

 function FindandReplaceReiseburoInternational() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(" _source_otb2")
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  var range = sheet.getRange(1, 1, lastRow, lastColumn)
  var to_replace = "REISEB��RO INTERNATIONAL";
  var replace_with = "Reiseburo International";
  var data  = range.getValues();
 
    var oldValue="REISEB��RO INTERNATIONAL";
    var newValue="Reiseburo International";
    var cellsChanged = 0;
 
    for (var r=0; r<data.length; r++) {
      for (var i=0; i<data[r].length; i++) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}

Get Doc ID.gs

 function _get_id() {
  return SpreadsheetApp.getActiveSpreadsheet().getId();
}

hgsyo

 //https://www.saperis.io/blog/how-to-copy-a-row-to-another-sheet-with-google-apps-script

// USING SET VALUES

 function copyRowsWithSetValues() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Source');
  
  let sourceRange = sourceSheet.getDataRange();
  let sourceValues = sourceRange.getValues();
  
  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;
  
  let targetSheet = spreadSheet.getSheetByName('Target');
  let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
  
  targetRange.setValues(sourceValues);
} 


// NO SET VALUES (Only works within the same document)

function copyRowsWithCopyTo() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Source');
  
  let sourceRange = sourceSheet.getDataRange();
  
  let targetSheet = spreadSheet.getSheetByName('Target');
  
  sourceRange.copyTo(targetSheet.getRange(1, 1));
} 

Import Data from Another Google Sheet.gs

//https://spreadsheet.dev/automatically-convert-excel-spreadsheets-to-google-sheets-using-apps-script

 function importDataFromSpreadsheet(spreadsheetId, sheetName) {
  let spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  let currentSpreadsheet = SpreadsheetApp.getActive();
  let newSheet = currentSpreadsheet.insertSheet();
  let dataToImport = spreadsheet.getSheetByName(sheetName).getDataRange();
  let range = newSheet.getRange(1,1,dataToImport.getNumRows(), dataToImport.getNumColumns());
  range.setValues(dataToImport.getValues());
  return newSheet.getName();
}

let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);



  function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);
}

//***************
// FULL CODE

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Import Excel file")
    .addItem("Import Excel file from Drive", "main")
    .addToUi();
}

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
}

let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);
}

function toast(message) {
  SpreadsheetApp.getActive().toast(message);
}

function promptUser(message) {
  let ui = SpreadsheetApp.getUi();
  let response = ui.prompt(message);
  if(response != null && response.getSelectedButton() === ui.Button.OK) {
    return response.getResponseText();
  } else {
    return null;
  }
}

function convertExcelToGoogleSheets(fileName) {
  let files = DriveApp.getFilesByName(fileName);
  let excelFile = null;
  if(files.hasNext())
    excelFile = files.next();
  else
    return null;
  let blob = excelFile.getBlob();
  let config = {
    title: "[Google Sheets] " + excelFile.getName(),
    parents: [{id: excelFile.getParents().next().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let spreadsheet = Drive.Files.insert(config, blob);
  return spreadsheet.id;
}

function importDataFromSpreadsheet(spreadsheetId, sheetName) {
  let spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  let currentSpreadsheet = SpreadsheetApp.getActive();
  let newSheet = currentSpreadsheet.insertSheet();
  let dataToImport = spreadsheet.getSheetByName(sheetName).getDataRange();
  let range = newSheet.getRange(1,1,dataToImport.getNumRows(), dataToImport.getNumColumns());
  range.setValues(dataToImport.getValues());
  return newSheet.getName();
}

Move File

 // MOVE FILE
DriveApp.getFileById(form.getId()).moveTo(DriveApp.getFolderById('xxx'))

Text Finder.gs

 // TEXT FINDER

// Creates  a text finder.
var textFinder = sheet.createTextFinder('dog');

// Returns the first occurrence of 'dog' in the sheet.
var firstOccurrence = textFinder.findNext();

// Replaces the last found occurrence of 'dog' with 'cat' and returns the number
// of occurrences replaced.
var numOccurrencesReplaced = findOccurrence.replaceWith('cat');

Trigger a Webhook.gs

function onEdit(e) {
  //Set a comment on the edited cell to indicate when it was changed.

var SheetName = "Projects"
  var url ="my webhook url";

if(SpreadsheetApp.getActiveSheet().getName() == SheetName){
  var row1 = SpreadsheetApp.getActiveSheet().getDataRange().getValues()[0];

  var index = e.range.rowStart;
  
  var sht = SpreadsheetApp.getActiveSheet();
  var rng = sht.getRange(index, 1, 1, 5);

  var columnName = null;

  var rangeArray = rng.getValues();
  var payload = new Object();

  payload["id"] = index;
for(i=0;i<row1.length;i++){
columnName = nextString(columnName);
var column = "Column"+columnName;
  payload[column] = rangeArray[0][i];
  payload[column+"_Heading"]=row1[i];
}
    var options = {
        "method": "post",
        "headers": {},
        "payload": payload
    };
    console.log(payload);
    var response = UrlFetchApp.fetch(url, options);
}
}

function nextString(str) {
    if (! str)
        return 'A'  // return 'A' if str is empty or null

    let tail = ''
    let i = str.length -1
    let char = str[i]
    // find the index of the first character from the right that is not a 'Z'
    while (char === 'Z' && i > 0) {
        i--
        char = str[i]
        tail = 'A' + tail   // tail contains a string of 'A'
    }
    if (char === 'Z')   // the string was made only of 'Z'
        return 'AA' + tail
    // increment the character that was not a 'Z'
    return str.slice(0, i) + String.fromCharCode(char.charCodeAt(0) + 1) + tail
}

UI Triggers.gs

 function runScript() {
  stopScript();
  ScriptApp.newTrigger("start").timeBased().everyHours(12).create();
}


function stopScript() {
  let triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}

function onOpen(e) {
  let sheetUi = SpreadsheetApp.getUi();

  sheetUi.createMenu('Menu')
  .addItem('Run', 'runScript')
  .addItem('Stop', 'stopScript')
  .addToUi();
}

User Prompt to Import Excel Sheet.gs

 function onOpen() {
  SpreadsheetApp.getUi().createMenu("Import Excel file")
    .addItem("Import Excel file from Drive", "main")
    .addToUi();
}

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
}

function toast(message) {
  SpreadsheetApp.getActive().toast(message);
}


function promptUser(message) {
  let ui = SpreadsheetApp.getUi();
  let response = ui.prompt(message);
  if(response != null && response.getSelectedButton() === ui.Button.OK) {
    return response.getResponseText();
  } else {
    return null;
  }
}



function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  toast(`Google Sheets File Id: ${spreadsheetId}`);
}

Webhook Intergration.gs

 
//https://hevodata.com/learn/google-sheets-webhooks-integration/


function doGet(e) {
var ss = SpreadsheetApp.getActive();
var rng = ss.getActiveSheet().getRange(2, 1, 10, 3)
var vals = rng.getValues()
Logger.log(vals)
return ContentService.createTextOutput(JSON.stringify(vals)).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
return HtmlService.createHtmlOutput(“post request received”);
}



function currentTime() {
var d = new Date();
var currentTime = d.toLocaleTimeString()
return currentTime;
}
function CELL_CHANGED(row) {
var options = {
'method' : 'post',
'payload’ : JSON.stringify(row)
};

UrlFetchApp.fetch('http://requestbin.net/r/1hqonni1', options);
return "UpdatedAt: "+ currentTime()
}


// MOVE FILE
DriveApp.getFileById(form.getId()).moveTo(DriveApp.getFolderById('xxx'))

// Copy Sheet
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheet = source.getSheets()[0];

var destination = SpreadsheetApp.openById('ID_GOES HERE');
sheet.copyTo(destination);